主页 / dba / 问题

问题[mysql](dba)

Martin Hope
Sidharth Samant
Asked: 2024-11-15 21:47:21 +0800 CST
  • 6

我有这两张桌子

CREATE TABLE `users` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `status` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=330031656 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;


CREATE TABLE `user_meta` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `meta_id` bigint NOT NULL,
  `value` bigint NOT NULL,
  PRIMARY KEY (`id`),
  KEY `usermeta_user_id_meta_type_meta_value` (`user_id`,`meta_id`,`value`),
  CONSTRAINT `user_meta_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16728 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;

客户要求，他们希望根据value，对特定的进行排序meta_id。例如，假设有 10meta_id个，客户选择meta_id=1111。在这种情况下，所有已关联的用户meta_id=1111都应按其第一顺序排序value，而其他没有关联的用户meta_id=1111可以按任何顺序排在底部。

曾经有人问过类似的问题，我也试图根据他们的答案提出疑问，但对我来说似乎不起作用。

这个答案和这个也讨论了使用if-else和case-when-then语句，但是当我尝试其中任何一个时

select u.id, um.meta_id, um.value from users u
inner join user_meta um on um.user_id = u.id
order by if(um.meta_id=1111, value, 1);

select u.id, um.meta_id, um.value from users u
inner join user_meta um on um.user_id = u.id
order by case um.meta_id
when 1111 then value else 1 end;

select u.id, um.meta_id, um.value from users u
inner join user_meta um on um.user_id = u.id
order by case 
when um.meta_id = 1111 then value else u.id end;

我明白了

+-----------+---------+------------+
| id        | meta_id | value      |
+-----------+---------+------------+
| 326480529 |     200 | 1730358000 |
| 326850494 |    1111 | 1730185200 |
| 326785127 |    1111 | 1730271600 |
| 326833934 |    1111 | 1730358000 |
| 326467136 |    1111 | 1730358000 |
| 328079379 |    1111 | 1730793600 |
+-----------+---------+------------+

我希望所有带有 的用户都meta_id=1111排在最上面，但他们既没有排在最上面，也没有在他们自己内部进行排序。同样，对于desc顺序，带有 的用户meta_id=1111应该排在最上面，按降序排列，而其他所有用户都可以排在最下面，例如

+-----------+---------+------------+
| id        | meta_id | value      |
+-----------+---------+------------+
| 328079379 |    1111 | 1730793600 |
| 326833934 |    1111 | 1730358000 |
| 326467136 |    1111 | 1730358000 |
| 326785127 |    1111 | 1730271600 |
| 326850494 |    1111 | 1730185200 |
| 326480529 |     200 | 1730358000 |
+-----------+---------+------------+

非常感谢任何帮助或指导，以解决此问题。非常感谢！

我还发布了INSERT两个表的一些语句，以便更轻松地在本地机器上复制

INSERT INTO `users` (`id`,`status`) VALUES (328079379,'active');
INSERT INTO `users` (`id`,`status`) VALUES (326833934,'active');
INSERT INTO `users` (`id`,`status`) VALUES (326467136,'deleted');
INSERT INTO `users` (`id`,`status`) VALUES (326785127,'inactive');
INSERT INTO `users` (`id`,`status`) VALUES (326850494,'removed');
INSERT INTO `users` (`id`,`status`) VALUES (326480529,'active');


INSERT INTO `user_meta` (`id`,`user_id`,`meta_id`,`value`) VALUES (13155,328079379,1111,1730793600);
INSERT INTO `user_meta` (`id`,`user_id`,`meta_id`,`value`) VALUES (13045,326833934,1111,1730358000);
INSERT INTO `user_meta` (`id`,`user_id`,`meta_id`,`value`) VALUES (13009,326467136,1111,1730358000);
INSERT INTO `user_meta` (`id`,`user_id`,`meta_id`,`value`) VALUES (13010,326785127,1111,1730271600);
INSERT INTO `user_meta` (`id`,`user_id`,`meta_id`,`value`) VALUES (13051,326850494,1111,1730185200);
INSERT INTO `user_meta` (`id`,`user_id`,`meta_id`,`value`) VALUES (13008,326480529,200,1730358000);
mysql
Martin Hope
SMich
Asked: 2024-11-13 09:55:57 +0800 CST
  • 5

对于 Rocky Linux 上的 MySQL 8.0.40，我的数据库目前使用 keyring 插件。我想迁移到 keyring 组件。它显示

[错误] [MY-013106] [服务器] 无法执行密钥环迁移：无法初始化目标密钥环。

我利用以下资源进行工作：

我看到有人成功关闭了加密，但我想避免这种情况。

我创建了清单：/usr/sbin/mysqld.my。它包含：

    {
      "components": "file://component_keyring_file"
    }

我检查了组件文件是否存在：

ls /usr/lib64/mysql/plugin/component_keyring_file.so #success

我创建了密钥环配置文件：**/usr/lib64/mysql/plugin/component_keyring_file.cnf**。其内容如下：

    {
      "path": "/var/lib/mysql/mysql-keyring/component_keyring_file",
      "read_only": false
    }

我为密钥环文件创建了目录：

    mkdir /var/lib/mysql/mysql-keyring
    chown mysql:mysql /var/lib/mysql/mysql-keyring

之后，我停止了 mysqld 并尝试运行迁移服务器。我尝试了以下变化：

    mysqld --user=mysql --defaults-file=/etc/my.cnf --keyring-migration-to-component  --keyring-migration-source=keyring_file.so  --keyring-migration-destination=component_keyring_file.so
    
    mysqld --user=mysql --keyring-migration-to-component  --keyring-migration-source=keyring_file.so  --keyring-migration-destination=component_keyring_file.so

我理解迁移用户不能是root，所以用户是mysql。另外component_keyring_file不能在数据目录中。我尝试了两种路径：

/var/lib/mysql/mysql-keyring # dir for mysql files

/var/lib/mysql-keyring

我更新了 component_keyring_file.cnf 并确保 mysql 用户拥有该目录。

mysql
Martin Hope
user216085
Asked: 2024-11-12 15:56:07 +0800 CST
  • 6

PostgreSQL您可以创建一个包含 4 个分区的表 ( hash)，并将每个分区定位在不同的服务器（远程 PostgreSQL 服务器）上，它被称为 ( )。现在在 MySQL中Foreign Data Wrapper，我听说有一个联合引擎，您可以使用它连接到远程服务器，但它只适用于表，而不是它的分区。我知道我可以使用NDB，但我想拥有多个 MySQL 服务器并自行管理分布的数据（基于表分区）。MySQL 中是否有任何解决方案可以创建一个表 ( Innodb) 并将其分区分布在多个远程 MySQL 服务器上？

mysql
Martin Hope
yaki_nuka
Asked: 2024-11-06 23:54:58 +0800 CST
  • 6

我使用的是 MySQL 8.0.32，我对内部转换有一些疑问，我不明白。除此之外，有时这种转换会显示在 SHOW FULL PROCESSLIST 中，因为它的执行时间太长。

这是我的桌子。

CREATE TABLE `provinces` (
 `ID` VARCHAR(2) NOT NULL COLLATE 'utf8mb4_swedish_ci',
 `PROVINCIA` VARCHAR(45) NOT NULL COLLATE 'utf8mb4_swedish_ci',
  PRIMARY KEY (`ID`)
  ) COLLATE='utf8mb4_swedish_ci' ENGINE=InnoDB;

我有 53 行这样的

ID 省
01 阿拉瓦
02 阿尔巴塞特

MySQL 函数是：

CREATE FUNCTION `get_provincia`( `id_provincia` VARCHAR(2)  )
RETURNS varchar(45)
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE desc_provincia VARCHAR(45) DEFAULT '-';

    SELECT PROVINCIA INTO desc_provincia
    FROM provinces
    WHERE ID = id_provincia;
    
    RETURN desc_provincia;
END

该功能的 SHOW STATUS 显示：

  • 字符集客户端: utf8mb4
  • 排序规则连接：utf8mb4_0900_ai_ci
  • 数据库排序规则：latin1_swedish_ci

该函数作为查询中的字段被调用。

SELECT customer_id, customer_name, ..., get_provincia(SUBSTRING(zip_code,1,2))...
FROM ...

当我使用 SHOW FULL PROCESSLIST 检查执行情况时，函数内部的查询显示如下：

SELECT provincia INTO desc_provincia
FROM provincias
WHERE id =  NAME_CONST('id_provincia',_latin1'15' COLLATE 'latin1_swedish_ci')

因此，我有以下问题：

  1. 为什么 MySQL 强制使用 NAME_CONST？
  2. 这会给性能带来影响吗？我不明白为什么非常简单的查询会在 SHOW FULL PROCESSLIST 命令的“时间”列中显示“2”值，因为它需要 2 秒。

提前致谢。

mysql
Martin Hope
Avinash Pawar
Asked: 2024-11-04 01:36:28 +0800 CST
  • 4

我正在尝试使用选项从 bash 脚本捕获 mysql 表中删除的行数--execute，但似乎不起作用。我回答了一些问题，其中提到使用选项，它可以工作，但没有达到预期的效果。我使用了 mysql 命令和awk 和 sed-vv的各种组合，但似乎没有任何效果。-vvv --silent--skip-column-names

MySQL 客户端是mysql Ver 8.0.39 for Linux on aarch64 (MySQL Community Server - GPL)

https://stackoverflow.com/questions/2628310/how-to-get-number-of-rows-deleted-from-mysql-in-shell-script

https://stackoverflow.com/questions/1083866/how-to-get-number-of-rows-affected-while-executing-mysql-query-from-bash

如何在使用 MySQL -e,--execute 时输出 UPDATE/INSERT 查询结果？

以下是代码

deleted=$(mysql --login-path=lp1 --init-command="SET SQL_LOG_BIN = 0" -vv --silent --skip-column-names db -e "delete from table1 where column1 date_format('2022-04-01 00:00:00','%Y-%m-%d 00:00:00') and date_format('2023-03-31 23:59:59','%Y-%m-%d 23:59:59') order by column1 limit 20;")

echo ${deleted}

该语句删除了某些行，但打印了如下输出

-------------- delete from table1 where column1 between date_format('2022-04-01 00:00:00','%Y-%m-%d 00:00:00') and date_format('2023-03-31 23:59:59','%Y-%m-%d 23:59:59') order by column1 limit 20 -------------- Query OK, 20 rows affected --------------  1 row in set Bye

如果我再添加一行来获取 row_count，那么它会给我 -1

mysql --login-path=${LP_wallet_0} -e "select row_count()"

即使我在同一个删除查询中尝试 row_count()，它只会附加此行select row_count() -------------- 2 1 row in set Bye

--vv --silent --skip-column-names我尝试了和awk和也是的不同组合sed，但无法得到计数。

mysql
Martin Hope
BrownianBridge
Asked: 2024-11-03 19:21:24 +0800 CST
  • 7

我在 mysql 版本 8.3.0 中启用了默认密码功能。

我有意使用这个功能。

MySQL 正在写入错误日志：

''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead

每秒不到 100 次。几周内，这已经耗尽了我 80GB 服务器的硬盘空间。

我怎样才能防止 MySQL 抱怨此设置的使用？

题外话

我知道general_log = off禁用“常规”日志的参数和值对。这不会禁用错误日志。

如果我无法找到一种方法来停止这个错误，我可能会将错误日志位置改为/dev/null。

不过，无论哪种情况，如果能够删除该消息就更好了。

规格

MySQL 版本 8.3.0。Linux 版本 20.04.6（focal fossa）。

mysql
Martin Hope
eeeeeeeeeeeeeeeeeeeeeeeeeeeeee
Asked: 2024-10-28 22:09:07 +0800 CST
  • 5

我正在运行带有 InnoDB 表的 Mysql 8。我有一张包含 100k 个小行的表，使用率较高（每秒 10-50 个查询）。

我想使用 将表与其较新版本进行交换RENAME table TO old_table, replacement_table TO table。正在运行的查询会发生什么？它们会崩溃还是会正常结束？新查询是否等待重命名结束，或者它们可以使用新版本的表，而现有查询则使用旧版本完成？

总的来说，这是一种更新表格的可行方法吗？否则，我需要比较每一行是否有变化，查找新行和已删除的行，因此像这样交换表格似乎要简单得多。

谢谢

mysql
Martin Hope
Lui229
Asked: 2024-10-25 20:22:48 +0800 CST
  • 5

每天晚上 laravel 调度程序都会"mysqldump -h $dbHost -u $dbUsername -p$dbPassword $dbDatabase > $path/$filename"通过函数运行命令exec()。

有时转储会在某个随机位置损坏：

好的转储看起来像这样：

LOCK TABLES `action_categories_link` WRITE;
/*!40000 ALTER TABLE `action_categories_link` DISABLE KEYS */;
INSERT INTO `action_categories_link` VALUES (1,1,1),(10,2,1),(15,3,2);
/*!40000 ALTER TABLE `action_categories_link` ENABLE KEYS */;
UNLOCK TABLES;

坏的一面是：

LOCK TABLES `action_categories_link` WRITE;
/*!40000 ALTER TABLE `action_categories_link` DISABLE KEYS */;
INSERT INTO `action_categories_link` VALUES (1,1,1),(10,2,1),(15,3,/*!4/*40000 ALTER TABLE `action_categories_link` ENABLE KEYS */;
UNLOCK TABLES;

另外，我还看到了这一行：

LOCKLOCK TABLES `action_categories` WRITE;

我不能在应用程序运行时运行转储吗？典型的转储大小接近 2GB，但这些错误从一开始就出现。有些转储没有损坏，所以我毫无头绪。

在与数据库相同的 Linux 服务器上转储，不使用 docker。MySQL dump 10.13 Distrib 8.0.29

mysql
Martin Hope
Renaud Aubert
Asked: 2024-10-18 21:55:25 +0800 CST
  • 5

我正在尝试为用户添加在合同/站点/实验中以给定角色进行授权的能力。

在此处输入图片描述

更具体地说，我希望用户能够在合同上拥有角色 A，但同时在同一合同的特定站点上拥有角色 B，并且可能在该站点的实验上拥有角色 C。

User我的第一个想法是在和之间建立一个连接表，Role其中第三列存储合同，站点或实验的 ID。

在我看来，这个解决方案有很多问题：

  • 我需要一行来记录合同、每个授权站点和每个授权实验。它可以快速增长到很大。
  • 缺乏完整性。可以引用不存在的合约
  • 如果我想知道用户被授权的合同的所有站点和实验，我必须首先查询此合同的所有站点，然后查询这些站点的所有实验，然后找到引用这些 ID 的所有行。这似乎有点不靠谱。

我觉得这个解决方案可行，但我想知道是否还有其他方法？也许是一些我不知道的模式？

PS：我正在使用 MySQL v8

mysql
Martin Hope
Stephan Vos
Asked: 2024-10-18 21:22:50 +0800 CST
  • 5

几年前，我使用 Rick James 的分区和索引指南创建了这个，但现在再次阅读该教程后，我不太确定我是否理解正确，以及下表是否是最佳的。（指南参考）

我认为其中一个常规索引是多余的，不确定我是否应该忽略分区键列 (datetime) 或更改其中一个索引以使分区键列 (datetime) 位于最后。在分区内选择特定日期时间范围时，将其作为索引的一部分是否有助于提高性能？

另外，是否有主键（deviceService，datetime）并在自动增量（id）上有一个索引会更好吗？

下面的基于时间序列的表格每个月分区有大约 8000 多万行。它几乎总是针对特定的日期时间范围和 deviceServiceId 的子集进行查询。

分区纯粹是为了存档和表维护，而不是为了性能。

CREATE TABLE `serviceResultLinkState` 
(
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `deviceServiceId` int(10) unsigned NOT NULL,
  `dateTime` datetime NOT NULL DEFAULT current_timestamp(),
  `priority` tinyint(1) unsigned NOT NULL,
  `alias` varchar(20) NOT NULL,
  `active` tinyint(3) unsigned DEFAULT NULL,
  `stable` tinyint(3) unsigned DEFAULT NULL,
  `attempts` int(5) unsigned DEFAULT NULL,
  `retries` int(5) unsigned DEFAULT NULL,
  `resultState` tinyint(3) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`,`dateTime`),
  KEY `dt_ds_idx` (`deviceServiceId`),
  KEY `datetime_idx` (`dateTime`,`deviceServiceId`)
) ENGINE=InnoDB AUTO_INCREMENT=24814201874 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
 PARTITION BY RANGE  COLUMNS(`dateTime`)
(PARTITION `p_202405` VALUES LESS THAN ('2024-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION `p_202406` VALUES LESS THAN ('2024-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION `p_202407` VALUES LESS THAN ('2024-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION `p_202408` VALUES LESS THAN ('2024-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION `p_202409` VALUES LESS THAN ('2024-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION `p_202410` VALUES LESS THAN ('2024-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION `p_202411` VALUES LESS THAN ('2024-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION `p_max` VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);
mysql