我有一个10.6.8托管在 Amazon RDS ( db.m6g.4xlarge) 上的 MariaDB 实例 ( )。它启用了自动次要升级，不幸的是，它在上次触发时导致了数小时的停机时间。

在查看日志时，问题似乎是我们的一些最大的表需要在升级完成之前重建。

error : Table rebuild required. Please do "ALTER TABLE 'my_table' FORCE" or dump/reload to fix it!

每个都需要几个小时。最大的有大约 1.7 亿行和 20 列。

我想在最坏的情况下，我们可以安排停机时间。但是，如果有某种方法可以以不同的方式做事，这样这就不会成为未来的问题，那将是更可取的。

到目前为止，我调查过：

• 添加修剪以避免在其中一些表中保留不需要的旧数据。这使得它们足够小，重建不会导致数小时的停机
• 将来，做并安排我自己的小升级，mysqlcheck提前运行，至少事先知道哪些表可能需要重建。这仍然不理想，因为该ALTER TABLE 'my_table' FORCE命令锁定了表，这当然也会导致我们的应用程序停机
• 假定所需的表重建不太可能再次发生。实施自动重建，选择旧表的块并将它们插入新表，直到它们的内容匹配。然后删除旧表并将新表重命名为旧表的名称。这可行——但需要时间，并且每次需要重建表时都需要完成
• 将大表在逻辑上拆分为具有相同架构的不同表。如果数据访问模式不经常需要一次查询多个表，那么我可以将一次为重建完成的锁定限制为一个（较小的）表，而不是一次锁定重建大表

理想情况下，我希望能够再次打开自动次要版本升级，并且它在预先选择的窗口中以最短的停机时间“正常工作”。

我知道在 5.x 系列的某个时刻，MySQL的 shell从 切换readline到。editline我似乎找不到任何关于如何在我的设置中进行历史搜索的参考资料。

macos: Ventura 13.2.1
shell: zsh
mysql: 8.0.32
iTerm2: 3.4.19

我尝试~/.editrc按如下方式配置我的：

bind "^c" ed-search-prev-history 

但是Ctrl+r在 shell 中不做任何事情mysql。

我错过了什么？

这是我的示例数据集。在 MySQL 中寻找代码

期望的输出

代码：

create  table test_student (student varchar(25), Term char (2),
year int, Subject varchar(20));

insert into test_student
values ("abc","t1","2021","sub1"),
("abc","t2","2021","sub2"),
("abc","t3","2021",""),
("abc","t4","2021",""),
("abc","t1","2022",""),
("abc","t2","2022","sub3"),
("abc","t3","2022",""),
("abc","t4","2022",""),
("abc","t1","2023","sub4"),
("abc","t2","2023",""),
("abc","t3","2023","sub5"),
("XYZ","t1","2021","sub1"),
("XYZ","t2","2021","sub2"),
("XYZ","t3","2021",""),
("XYZ","t4","2021",""),
("XYZ","t1","2022",""),
("XYZ","t2","2022",""),
("XYZ","t3","2022","sub3"),
("XYZ","t4","2022",""),
("XYZ","T1","2023",""),
("XYZ","t2","2023","sub4"),
("XYZ","t3","2023","sub5");

---

下面的代码给我输出 abc: 3 xyz: 3

WITH cte AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY student, subject ORDER BY term) AS term_num
    FROM test_student
)
SELECT student,  MAX(gap_length) AS max_gap
FROM (
    SELECT student, 
        term_num - ROW_NUMBER() OVER (PARTITION BY student, year ORDER BY term) AS gap_length
    FROM cte
    WHERE subject = ''
) AS gaps
GROUP BY student;

有一张表格从在线服务中填充并每周更新。

这在一个单独的（第二个）数据库上进行。

该表的大小约为 300MB（150 万行），由每周下载 CSV 数据的 cron 处理。此 CSV 文件大小为 3GB（400 万行），其中的行和列被过滤以仅保留数据的子集。列的顺序可能会改变。过去有。所以我们甚至无法根据第一行进行预测。甚至行标题名称也可能会更改。

所以它是一个完全转储，并按一些硬设置的行名称进行过滤。

在我的 Django 视图中，它是一个 DRF 驱动的视图，我的 API URL 需要 3 秒 (0:00:03.263031) 来执行。

SELECT DISTINCT `Region` AS `Region`
FROM `my-table-name`
WHERE `flag` = 'condition'
LIMIT 0, 100

问题是，如果它只是我在页面上获取的这个端点，它就不会终止。但是当用户点击 INPUT 元素时，单个页面上有许多端点被获取。每次触发 3 秒太长了。

我还能做些什么来优化表/查询？

我有两个 MySQL 表，users和supervisors. 在这个简化的示例中，users只有一个 ID 和一个用户名，并且supervisors是一个将两个用户链接在一起的表。

我想构建一个返回两列的查询 - 一个用户名和他们主管的用户名列表。

我已经弄清楚如何通过此查询仅使用主管的 ID 来完成此操作：

SELECT U.username, GROUP_CONCAT(S.supervisorId) AS Supervisors FROM users AS U LEFT JOIN `supervisors` AS S ON S.userId=U.userID WHERE 1 GROUP BY U.userID;

例如，它返回1,3给史蒂夫的主管。相反，我希望它返回Carol, Dave（最好按字母顺序在空格后加逗号，但如有必要，我可以稍后处理）。

这可能吗？我不确定如何实现它。谢谢！

小提琴手

CREATE TABLE IF NOT EXISTS `users` (
  `userId` int(6) unsigned NOT NULL,
  `username` varchar(200) NOT NULL,
  PRIMARY KEY (`userId`)
) DEFAULT CHARSET=utf8;
INSERT INTO `users` (`userId`, `username`) VALUES
  ('1', 'Dave'),
  ('2', 'Jane'),
  ('3', 'Carol'),
  ('4', 'Steve');
  
  CREATE TABLE IF NOT EXISTS `supervisors` (
  `supervisorId` int(6) unsigned NOT NULL,
  `userId` int(6) NOT NULL,
  PRIMARY KEY (`supervisorId`,`userId`)
) DEFAULT CHARSET=utf8;

INSERT INTO `supervisors` (`supervisorId`, `userId`) VALUES
  ('1', '2'),
  ('1', '3'),
  ('1', '4'),
  ('3', '4');

我在处理查询时相对较新，所以让我提前道歉。

我的sql是

$sql1 = SELECT 
AVG( ST_Distance_Sphere( point( `event`.`event_location_longitude`, `event`.`event_location_latitude` ), point( `user`.`user_location_longitude`, `user`.`user_location_latitude` ) ) *.000621371192 ) as market_miles_flown_international
 
FROM `event_contract` 
LEFT JOIN `event` ON `event_id` = `event_contract_event_id` 
LEFT JOIN `event_bid` ON `event_bid_event_id` = `event_id` 
LEFT JOIN `user` ON `user_id` = `event_bid_td_id` 

WHERE `event_bid_transportation_method` = 'airplane' AND event_location_country <> 'US'

$sql2 = SELECT 
AVG( ST_Distance_Sphere( point( `event`.`event_location_longitude`, `event`.`event_location_latitude` ), point( `user`.`user_location_longitude`, `user`.`user_location_latitude` ) ) *.000621371192 ) as market_miles_flown_domestic 

FROM `event_contract` 
LEFT JOIN event ON event_id = event_contract_event_id 
LEFT JOIN `event_bid` ON `event_bid_event_id` = `event_id` 
LEFT JOIN `user` ON `user_id` = `event_bid_td_id` 

WHERE `event_bid_transportation_method` = 'airplane' AND event_location_country = 'US' AND user_location_country = 'US'

$sql3 = SELECT 
AVG( ST_Distance_Sphere( point( `event`.`event_location_longitude`, `event`.`event_location_latitude` ), point( `user`.`user_location_longitude`, `user`.`user_location_latitude` ) ) *.000621371192 ) as market_miles_driven 

FROM `event_contract` 
LEFT JOIN event ON event_id = event_contract_event_id 
LEFT JOIN `event_bid` ON `event_bid_event_id` = `event_id` 
LEFT JOIN `user` ON `user_id` = `event_bid_td_id` 

WHERE `event_bid_transportation_method` IN ('auto', 'train')

仔细观察 from 部分和 left join 部分很常见，我想以一种方式组合

SELECT

AVG( ST_Distance_Sphere( point( `event`.`event_location_longitude`, `event`.`event_location_latitude` ), point( `user`.`user_location_longitude`, `user`.`user_location_latitude` ) ) *.000621371192 ) as market_miles_flown_international,

AVG( ST_Distance_Sphere( point( `event`.`event_location_longitude`, `event`.`event_location_latitude` ), point( `user`.`user_location_longitude`, 
`user`.`user_location_latitude` ) ) *.000621371192 ) as market_miles_flown_domestic,


AVG( ST_Distance_Sphere( point( `event`.`event_location_longitude`, `event`.`event_location_latitude` ), point( `user`.`user_location_longitude`, `user`.`user_location_latitude` ) ) *.000621371192 ) as market_miles_driven   

FROM `event_contract` 
LEFT JOIN event ON event_id = event_contract_event_id 
LEFT JOIN `event_bid` ON `event_bid_event_id` = `event_id` 
LEFT JOIN `user` ON `user_id` = `event_bid_td_id` 

WHERE where1 of $sql1, where2 of $sql2, where3 of $sql3

有什么办法吗？谢谢

一般来说，我想按一列或另一列分组：

SELECT count(*)
FROM foo
GROUP BY column1 OR column2

特别是，我想识别给定表中的重复项，下面是一个数据最少的示例。

最小的例子

| id | name       | birth_date   | email                        |
|---:|:-----------|:-------------|:-----------------------------|
|  1 | 'Gamow'    | '1904-03-04' | '[email protected]'    |
|  2 | 'Gamow'    | NULL         | NULL                         |
|  3 | 'Gamow'    | '1904-03-04' | NULL                         |
|  4 | 'Gamow'    | '1904-03-04' | '[email protected]'    |
|  5 | 'Gamow'    | NULL         | '[email protected]'    |
|  6 | 'Feynman'  | '1918-05-11' | '[email protected]' |
|  7 | 'Feynman'  | '1918-05-11' | NULL                         |
|  8 | 'Poincaré' | '1854-04-29' | '[email protected]'  |

如果两个人具有相同的名字和（相同的出生日期或相同的电子邮件），则他们被认为是重复的。
我想找到一个查询谁给

• MIN(id)在重复项中：要保留的行
• 1+ 个其他 ID：要删除的行

我写了这个查询

SELECT 
    MIN(p.id) AS id_tokeep,
    REPLACE(
        GROUP_CONCAT(p.id ORDER BY p.id ASC SEPARATOR ','),
        CONCAT(MIN(p.id), ','),
        ''
    ) AS ids_todelete,
    MIN(p.name) AS name
FROM people AS p
WHERE p.birth_date IS NOT NULL OR p.email IS NOT NULL 
GROUP BY p.name, (p.birth_date IS NOT NULL) OR (p.email IS NOT NULL)
HAVING COUNT(id) > 1
ORDER BY id_tokeep;

哪个有效：

| id_tokeep | ids_todelete | name      |
|----------:|--------------|-----------|
|         1 | '3,4,5'      | 'Gamow'   |
|         6 | '7'          | 'Feynman' |

除了：

• OR感觉就像一个 hack（因为我在任何地方都没有发现inside a clause的使用GROUP BY），
• 它有时会产生警告 « Truncated incorrect DOUBLE value: '[email protected]'»
• 对于真实数据，它会返回“奇怪”的结果（这似乎证实了它是一个 hack）。

因此，我的问题是：

• 在 GROUP BY 子句中使用 OR 是否“合法”？
• 如果不是，如何在 GROUP BY 中没有 OR 的情况下重写此查询？

生成最小示例的 SQL 代码

DROP TABLE IF EXISTS people;

CREATE TABLE people (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    birth_date DATE DEFAULT NULL,
    email VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO people (name, birth_date, email)
VALUES
    ('Gamow', '1904-03-04', '[email protected]'),  -- 1
    ('Gamow', NULL, NULL),  -- 2
    ('Gamow', '1904-03-04', NULL),  -- 3
    ('Gamow', '1904-03-04', '[email protected]'),  -- 4
    ('Gamow', NULL, '[email protected]'),  -- 5
    ('Feynman', '1918-05-11', '[email protected]'),  -- 6
    ('Feynman', '1918-05-11', NULL),  -- 7
    ('Poincaré', '1854-04-29', '[email protected]')  -- 8
;

其他试用查询

我尝试了两个自连接，但随后我很难对重复项进行分组：

SELECT 
    p1.id AS patient_id,
    p2.id AS patient_match_birth_date_id,
    p3.id AS patient_match_email_id
FROM people AS p1
    JOIN people AS p2 ON p2.birth_date = p1.birth_date
    JOIN people AS p3 ON p3.email = p1.email
ORDER BY p1.id, p2.id, p3.id;

我尝试将出生日期的重复项与电子邮件中的重复项结合起来，但它返回了冗余数据：

WITH patient_matches AS (
    (
        SELECT 
            p1.id AS patient_id,
            p2.id AS patient_match_id
        FROM people AS p1
            JOIN people AS p2 ON p2.birth_date = p1.birth_date
        ORDER BY p1.id ASC 
    )
    UNION ALL 
    (
        SELECT 
            p1.id AS patient_id,
            p2.id AS patient_match_id
        FROM people AS p1
            JOIN people AS p2 ON p2.email = p1.email
        ORDER BY p1.id ASC 
    )
    ORDER BY patient_match_id
)
SELECT json_arrayagg(pm.patient_id)
FROM patient_matches AS pm
GROUP BY pm.patient_match_id;

MySQL 版本 5.7.29-日志

我的表（事件）有 EventID、EventTypeID、StartDateTime、EndDateTime

我想提取月份的名称，然后提取该月记录的日期，并输出如下：“September: 21, 22, 30”。

每行应显示该月有事件的任何记录的输出，例如

January: 21, 22
March: 2, 3, 9
...
December: 3, 6, 9
...

注：日期按升序排列，月份按顺序排列。而且，它应该基于EventTypeID（分组依据）。

我试过搜索这个，但我需要帮助来寻找包括一个月中每一天都存在记录的帮助。

这是我试过的代码，但它离我需要的还很远。

SELECT
  eventid, EventTypeID,

  IF (
    DAY (startdatetime) = DAY(EndDateTime),
    concat( DATE_FORMAT(StartDateTime, '%M'),': ',  DAY (StartDateTime)), 
    concat( DATE_FORMAT(StartDateTime, '%M'),': ',  DAY (StartDateTime),        ' & ',      DAY (EndDateTime)   )
) as Days
FROM
    `events`
WHERE
    `events`.StartDateTime > now()
Group by EventTypeID, month(StartDateTime)
order by month(StartDateTime)

创建表：

CREATE TABLE `Events` (
`EventID`  int NOT NULL ,
`EventTypeID`  int NULL ,
`StartDateTime`  datetime NULL ,
`EndDateTime`  datetime NULL ,
PRIMARY KEY (`EventID`)
)
;

示例数据（25 行）：

INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('11139', '670', '2023-03-28 08:30:00', '2023-03-29 08:30:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10937', '1', '2023-03-21 08:00:00', '2023-03-21 14:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('11161', '713', '2023-03-29 08:30:00', '2023-03-30 08:30:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10938', '64', '2023-03-21 08:00:00', '2023-03-21 09:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10969', '61', '2023-03-22 08:00:00', '2023-03-22 14:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10967', '27', '2023-03-23 09:00:00', '2023-03-23 15:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('11093', '126', '2023-03-27 10:00:00', '2023-03-27 19:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('11094', '710', '2023-03-27 10:00:00', '2023-03-27 18:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10970', '3', '2023-04-12 08:30:00', '2023-04-12 23:30:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10939', '1', '2023-04-26 08:30:00', '2023-04-26 14:30:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10972', '60', '2023-04-12 08:30:00', '2023-04-12 14:30:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10973', '61', '2023-04-14 08:00:00', '2023-04-14 14:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10917', '721', '2023-04-01 08:30:00', '2023-04-02 08:30:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('11180', '21', '2023-04-15 10:00:00', '2023-04-15 18:30:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10953', '53', '2023-04-05 09:00:00', '2023-04-05 21:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('11186', '673', '2023-04-19 10:00:00', '2023-04-19 15:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10954', '64', '2023-04-05 09:00:00', '2023-04-05 10:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('11091', '126', '2023-04-22 10:00:00', '2023-04-22 19:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10901', '670', '2023-04-11 08:30:00', '2023-04-12 08:30:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('11092', '710', '2023-04-22 10:00:00', '2023-04-22 18:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('11130', '721', '2023-05-20 08:30:00', '2023-05-22 12:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('11151', '670', '2023-05-21 08:00:00', '2023-05-22 08:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10941', '53', '2023-05-04 09:00:00', '2023-05-04 21:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10995', '27', '2023-05-23 09:00:00', '2023-05-23 15:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10942', '64', '2023-05-04 09:00:00', '2023-05-04 10:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10945', '1', '2023-05-26 08:00:00', '2023-05-26 14:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10977', '61', '2023-05-06 08:00:00', '2023-05-06 14:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10997', '3', '2023-05-30 08:00:00', '2023-05-30 23:00:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10892', '25', '2023-05-16 08:30:00', '2023-05-16 14:30:00');
INSERT INTO `events` (`EventID`, `EventTypeID`, `StartDateTime`, `EndDateTime`) VALUES ('10999', '60', '2023-05-30 08:00:00', '2023-05-30 14:00:00');

感觉group_concat这里可能有用，但是不清楚怎么集成。

考虑具有全局数据库访问权限的用户：

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY PASSWORD 'xxx' WITH GRANT OPTION

由于遗留问题和涉及的数据库数量，我想REVOKE从该用户那里获得一小部分数据库的许可，而不要管其余部分。当然，这不起作用：

REVOKE ALL PRIVILEGES ON mydatabase.* FROM 'myuser'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'myuser' on host '%'

我明白为什么会这样，因为访问是全局的，但是 MySQL 有我可以使用的排除机制吗？

如果没有，任何人都可以指向一个脚本，我可以在其中提取所有数据库名称以使授权更细化吗？将全局拨款分解为单独的每个数据库拨款的陷阱是什么？

我通过 5.7.36 安装在 Red Hat 7.9 上运行了 mysql 社区安装程序。

首先，我为所有数据库运行了 xtrabackup 然后我停止了 mysqld 然后我运行了 yum install 一切都结束了我在尝试重新启动 mysqld 时在错误日志中收到以下错误。

[System] [MY-013381] [Server] Server upgrade from '50700' to '80032' started.
[ERROR] [MY-013178] [Server] Execution of server-side SQL statement 'ALTER TABLE mysql.plugin TABLESPACE = mysql; ' failed with error code = 3825, error message = 'Request to create 'encrypted' table while using an 'unencrypted' tablespace.'.
[ERROR] [MY-013380] [Server] Failed to upgrade server.
[ERROR] [MY-010119] [Server] Aborting

我的数据库使用密钥环加密。

my.cnf 配置文件：

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]

early-plugin-load=keyring_file.so

sql_mode=""

bind-address = 0.0.0.0
datadir = /var/lib/mysql

innodb_file_per_table = ON
key_buffer_size = 16M
log-error = /var/log/mysqld.log
max_allowed_packet = 1024M
max_binlog_size = 1000M
max_connections = 150
port = 3306
skip-external-locking
socket = /var/lib/mysql/mysql.sock
tmpdir = /tmp
user = mysql

#server-id = 11211
#log-bin = mysql-bin

innodb_buffer_pool_size = 2560M
innodb_log_buffer_size = 16M

innodb_redo_log_capacity=2GB

[mysqldump]
max_allowed_packet = 1024M
host = localhost

检查了 /var/lib/mysql 目录和文件以及 /var/lib/mysql-keyring/keyring 文件的权限。