我的公众号区别于别人，我专门讲一些 MySQL 奇怪的知识，这些大多数是没有人写过的。今天我们继续讲一些奇怪的知识点，如果你 "奇怪的知识+N" 了，请留言“你赢了”，如果 "无趣的知识+N"，请留言“你输了”，及时让我知道，我会调整公众号的内容，满足读者。
关于comment
1. MySQL comment 语法
实际上他只是表结构里的一个语法。
我们的开发规范常说的，必须给字段、表写注释，这个注释是指 MySQL 的 comment 语法，如下:
create table people(
id int primary key auto_increment comment "自增ID",
name varchar(30) comment "人名"
) comment "人名表";
mysql> show create table people;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| people | CREATE TABLE `people` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`name` varchar(30) DEFAULT NULL COMMENT '人名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='人名表' |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
comment 语句能直接进入表结构，作为表设计的一个注释。
2. sql comment 语法
另外一种 comment 指的是 sql comment，也就是 sql 语句的注释，MySQL 支持以下三种 sql comment 样式。
-
从一个 #字符到行尾。 -
从一个 --序列到行尾。在 MySQL 中，--（双破折号）注释样式要求第二个破折号后跟至少一个空格或控制字符（例如空格、制表符、换行符等）。 -
从一个 /*序列到后面的*/序列，就像在 C 编程语言中一样。此语法使注释可以扩展到多行，因为开始和结束序列不必在同一行。
以下是例子:
mysql> SELECT 1+1; # This comment continues to the end of line
mysql> SELECT 1+1; -- This comment continues to the end of line
mysql> SELECT 1 /* this is an in-line comment */ + 1;
mysql> SELECT 1+
/*
this is a
multiple-line comment
*/
1;
sql comment 的注意点
1. sql comment 不会存进表结构
sql comment 区别于 MySQL comment，只是对 sql 做注释，是不会存到数据库的。请留意以下例子的 3 个注释位置，并不会存到表结构元数据里。
mysql> create table people(
-> id /* 注释1 */ int primary key auto_increment comment "自增ID",
-> name varchar(30) comment "人名" -- 注释2
-> ) comment "人名表"; # 注释3
Query OK, 0 rows affected (0.02 sec)
mysql> show create table people;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| people | CREATE TABLE `people` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`name` varchar(30) DEFAULT NULL COMMENT '人名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='人名表' |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2. sql comment /* 注释 */ 语法比较自由，可以在 sql 里任何位置，并且支持单行注释，也支持多行注释，最常用。
mysql> insert into people values(1,'fander1'/* 你好1 */);
Query OK, 1 row affected (0.01 sec)
mysql> insert into people /* 你好2 */values(2,'fander2');
Query OK, 1 row affected (0.01 sec)
# 错误示范，把注释当数据一起存进表里了
mysql> insert into people values(3,'fander3/* 你好3 */');
Query OK, 1 row affected (0.00 sec)
# 错误示范，在保留字中间插了注释
mysql> insert into people v/* 你好4 */alues(4,'fander4');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'v alues(4,'fander4')' at line 1
mysql> insert /* 你
/*> 好
/*> 5 */ into people values(5,'fander5');
Query OK, 1 row affected (0.01 sec)
mysql> select * from people;
+----+----------------------+
| id | name |
+----+----------------------+
| 1 | fander1 |
| 2 | fander2 |
| 3 | fander3/* 你好3 */ |
| 5 | fander5 |
+----+----------------------+
3 rows in set (0.00 sec)
3. sql comment 实际上是 sql 的一部分，所以会存进任何涉及记录 sql 语句的 log 里，我认为包括 slow.log、general_log、binlog、审计日志 等。
我用 navicat 测试了 slow log、general log、binlog 能证实我的想法:
日志显示如下:
# slow log
[root@fander ~]# tail -2 /data/mysql/mysql3307/data/fander-slow.log
SET timestamp=1656329353;
select sleep(10) /* 你好navicat */;
# general log
2022-06-27T19:29:03.847879+08:00 17 Query begin
2022-06-27T19:29:03.860918+08:00 17 Query select sleep(10) /* 你好navicat */
2022-06-27T19:29:13.882600+08:00 17 Query insert into people(name) values('fander'/* 你好navicat */)
2022-06-27T19:29:13.909462+08:00 17 Query commit
# binlog
# 需要开启 binlog_rows_query_log_events=on
/*!*/;
# at 2193
#220627 19:29:13 server id 1983307 end_log_pos 2273 Rows_query
# insert into people(name) values('fander'/* 你好navicat */)
关于 sql comment 奇怪的知识1
这里有一个奇怪的知识点，区别于 navicat 这种 UI 客户端，用 mysql 客户端执行 sql，日志不会记录 sql comment
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select sleep(10) /* 你好 mysql 客户端 */;
+-----------+
| sleep(10) |
+-----------+
| 0 |
+-----------+
1 row in set (10.01 sec)
mysql> insert into people(name) values('fander'/* 你好 mysql 客户端 */);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
日志显示如下:
# slow log
[root@fander ~]# !4745
tail -2 /data/mysql/mysql3307/data/fander-slow.log
SET timestamp=1656330131;
select sleep(10);
# general log
2022-06-27T19:42:01.695630+08:00 18 Query begin
2022-06-27T19:42:01.696036+08:00 18 Query select sleep(10)
2022-06-27T19:42:11.701683+08:00 18 Query insert into people(name) values('fander' )
2022-06-27T19:42:11.821853+08:00 18 Query commit
# binlog
/*!*/;
# at 2524
#220627 19:42:11 server id 1983307 end_log_pos 2586 Rows_query
# insert into people(name) values('fander' )
后面我发现这个 mysql 客户端的默认行为是可以修改的，-c参数。
[root@fander ~]# mysql --help |grep comments
-c, --comments Preserve comments. Send comments to the server. The
default is --skip-comments (discard comments), enable
with --comments.
comments FALSE
现在，客户端也记录 sql comment 了。
[root@fander ~]# mysql fander -c -e "select sleep(10)/* 客户端注释*/"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| sleep(10) |
+-----------+
| 0 |
+-----------+
[root@fander ~]# tail -2 /data/mysql/mysql3307/data/fander-slow.log
SET timestamp=1656349516;
select sleep(10)/* 客户端注释*/;
关于 sql comment 奇怪的知识2
有个开源客户端会“卖广告”！他叫 DBeaver，执行 SQL 的界面如下:
日志显示如下：
自己给 SQL 的前面加注释。
# slow log
SET timestamp=1656330418;
/* ApplicationName=DBeaver 22.1.1 - SQLEditor <Script.sql> */ select sleep(10)
# general log
2022-06-27T19:46:48.013159+08:00 12 Query /* ApplicationName=DBeaver 22.1.1 - SQLEditor <Script.sql> */ select sleep(10)
关于 sql comment 的另外一件事——MySQL 对标准 SQL 的扩展
请考虑以下 SQL，下面这个类似的 SQL 我们在 mysqldump 备份时通过 show processlist 观察，经常能看到。
SELECT /*!40001 SQL_NO_CACHE */ * FROM test;
他并不是注释，/*! xxx */ 这种是 MySQL 对标准 SQL 的扩展，是一种特殊语法。这种对标准 SQL 扩展的语法发明的目标，是为了让同一条 SQL 可以兼容别的数据库。他的原理就是，/*! xxx */ 可以在 MySQL 识别和执行，在其他数据库会认为这是注释而不执行，例如
# STRAIGHT_JOIN 是 MySQL 的特殊语句语法，强制让表关联按左右顺序关联
SELECT /*! STRAIGHT_JOIN */ col1 FROM table1,table2 WHERE ...
# 以上语句在 MySQL 等价于
SELECT STRAIGHT_JOIN col1 FROM table1,table2 WHERE ...
# 在其他数据库里会认为是注释，会等价于
SELECT col1 FROM table1,table2 WHERE ...
这样，这条 SQL 就可以跨平台兼容了。
我再举个例子，证明 /* */ 在 MySQL 是注释，而 /*！*/ 不是，是要执行的。
# 相当于 select a from test_group_by;
mysql> select a/* ,b */ from test_group_by;
+------+
| a |
+------+
| 6 |
| 5 |
| 4 |
| 3 |
| 2 |
| 1 |
+------+
6 rows in set (0.00 sec)
# select a,b from test_group_by;
mysql> select a/*! ,b */ from test_group_by;
+------+------+
| a | b |
+------+------+
| 6 | abc |
| 5 | def |
| 4 | abc |
| 3 | abc |
| 2 | def |
| 1 | def |
+------+------+
6 rows in set (0.01 sec)
然后，我再解释一下，前面我们看到的奇怪的数字 !40001 是什么意思。他的意思是大于等于 4.0.1 版本才会执行。我们来测试一下。
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.36-log |
+------------+
1 row in set (0.00 sec)
mysql> select a/*!50737 ,b */ from test_group_by;
+------+
| a |
+------+
| 6 |
| 5 |
| 4 |
| 3 |
| 2 |
| 1 |
+------+
6 rows in set (0.00 sec)
mysql> select a/*!50736 ,b */ from test_group_by;
+------+------+
| a | b |
+------+------+
| 6 | abc |
| 5 | def |
| 4 | abc |
| 3 | abc |
| 2 | def |
| 1 | def |
+------+------+
6 rows in set (0.00 sec)
好了，我讲完了 comment 了，我现在要讲 hint
关于hint
hint 是在某些特定的场景下人工协助干预 MySQL 优化器的 sql 语法工具，使他生成我们想要的执行计划。一般来说，优化器的执行计划都是最优化的，不过在某些特定场景下，执行计划可能不是最优化，我们就可以使用 hint。
在 MySQL 中，SQL 优化里最常见的 hint 用法是:
# 提示优化器可选择的索引列表
SELECT * FROM table1 USE INDEX (col1_index,col2_index)
WHERE col1=1 AND col2=2 AND col3=3;
# 提示优化器忽略某个索引
SELECT * FROM table1 IGNORE INDEX (col3_index)
WHERE col1=1 AND col2=2 AND col3=3;
# 提示优化器强制走某个索引
SELECT * FROM table1 FORCE INDEX (col1_index)
WHERE col1=1 AND col2=2 AND col3=3;
以上是 5.6、5.7、8.0 都能用的语法。但 5.7 开始，hint 有了新语法，和前面提到的使用 sql comment 对标准 SQL 的扩展一样，hint 的新语法，也是利用 /* */ 做扩展。这也是我为什么把 comment 和 hint 放在一起说的原因。
因为 oracle 也是用 /*+ hint */ 这种语法做 hint 的，我没测试，但我猜测有可能会冲突，也就是与 oracle 不兼容，oracle 不认为这是注释语句，所以新 hint 语法没有使同一条 SQL 可以兼容多个数据库平台这个好处。
新的 hint 语法是/*+ */，举例如下:
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
FROM t3 WHERE f1 > 30 AND f1 < 33;
SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...;
EXPLAIN SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...;
新语法支持的 hint 如下:
可以看出来， 5.7 没有使用新语法来实现和替换 USE INDEX、IGNORE INDEX、FORCE INDEX 这三个常见的 hint。
到了 8.0 版本后，新 hint 语法支持更多的 hint 了，有不少的 hint 还是新开发出来的。
/*+ INDEX */ 替代了 FORCE INDEX/*+ NO_INDEX */ 替代了 IGNORE INDEX
在上面这些 hint 里，我估计我一般 SQL 优化就用这俩了。在此之外，我还发现两个好玩的 hint。
第一个是 MAX_EXECUTION_TIME (5.7版本加入)
作用是限制 SQL 执行时间，有了这招后没有人担心我执行大事务的问题了。
mysql> select /*+ MAX_EXECUTION_TIME(10) */ count(1) from sbtest1;
ERROR 1317 (70100): Query execution was interrupted
mysql> select /*+ MAX_EXECUTION_TIME(100) */ count(1) from sbtest1;
+----------+
| count(1) |
+----------+
| 1000000 |
+----------+
1 row in set (0.02 sec)
MAX_EXECUTION_TIME(10) 表示 10 毫秒内要执行完，我的 SQL 实际执行时间要 20ms，所以限制 MAX_EXECUTION_TIME(10) 会执行失败，限制 MAX_EXECUTION_TIME(100) 能执行成功。
第二个是 SET_VAR (8.0版本加入)
作用是，让我的某条 sql 生命周期里可以修改变量值，sql 结束后变量就还原了。
mysql> SELECT @@unique_checks;
+-----------------+
| @@unique_checks |
+-----------------+
| 1 |
+-----------------+
mysql> SELECT /*+ SET_VAR(unique_checks=OFF) */ @@unique_checks;
+-----------------+
| @@unique_checks |
+-----------------+
| 0 |
+-----------------+
mysql> SELECT @@unique_checks;
+-----------------+
| @@unique_checks |
+-----------------+
| 1 |
+-----------------+
这个参数的发明，旨在用更简单的方法替代以前的解决方案:
SET @saved_val = @@SESSION.var_name;
SET @@SESSION.var_name = value;
SELECT ...
SET @@SESSION.var_name = @saved_val;
先保存旧变量值，然后改变量值，执行查询，再改回原来变量值。
并不是所有的参数都支持 SET_VAR hint，某个具体参数是否支持 SET_VAR hint，在官方文档上加了一列说明，例如 sql_mode 这个参数就支持 SET_VAR hint。
最后我用一个综合的案例，把 comment 和 hint 都塞进我的实验里。
案例里，为什么我要修改 sql_mode，请阅读我的这篇文章《我为什么不赞成sql_mode去掉ONLY_FULL_GROUP_BY》(点击跳转)
# 没有设置主键索引
create table test_group_by(
a INT,
b VARCHAR(10),
c INT
);
# 插入 6 条数据
INSERT INTO test_group_by
VALUES
(1, 'def', 6),
(2, 'def', 5),
(3, 'abc', 4),
(4, 'abc', 3),
(5, 'def', 2),
(6, 'abc', 1);
mysql> select * from test_group_by;
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | def | 6 |
| 2 | def | 5 |
| 3 | abc | 4 |
| 4 | abc | 3 |
| 5 | def | 2 |
| 6 | abc | 1 |
+------+------+------+
6 rows in set (0.00 sec)
mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
# 违反 ONLY_FULL_GROUP_BY 规则导致无法执行
mysql> select a,b,c,count(*) from test_group_by group by b;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'fander.test_group_by.a' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
# 使用 hint 使这条SQL临时设置sql_mode为空，没有 ONLY_FULL_GROUP_BY 限制，从而可以执行
mysql> select /*+ SET_VAR(sql_mode=" ") */ a,b,c,count(*) from test_group_by group by b;
+------+------+------+----------+
| a | b | c | count(*) |
+------+------+------+----------+
| 1 | def | 6 | 3 |
| 3 | abc | 4 | 3 |
+------+------+------+----------+
2 rows in set (0.00 sec)
# 我们能发现 b 列 group by 后，没有自动从小到大排序，而MySQL 5.7默认行为是group by会隐式排序
# 因为你看我能用 SET_VAR hint 语法就知道我是 8.0 版本了，8.0 的 group by 默认不排序的。
# 那么我们可以用刚才学到的 /*!80001 order by b */，让这个 SQL 对大于8.0.1 版本排序。
mysql> select /*+ SET_VAR(sql_mode=" ") */ a,b,c,count(*) from test_group_by group by b /*!80001 order by b */;
+------+------+------+----------+
| a | b | c | count(*) |
+------+------+------+----------+
| 3 | abc | 4 | 3 |
| 1 | def | 6 | 3 |
+------+------+------+----------+
2 rows in set (0.00 sec)
还没完！我们加个餐，其实我的 MySQL 不是 8.0，而是9.88.77，至于为什么？请阅读《手把手教你编译安装 MySQL8.0.29》(点击跳转)
mysql> select @@version;
+---------------+
| @@version |
+---------------+
| 9.88.77-debug |
+---------------+
1 row in set (0.00 sec)
mysql> select /*+ SET_VAR(sql_mode=" ") */ a,b,c,count(*) from test_group_by group by b /*!80099 order by b */;
+------+------+------+----------+
| a | b | c | count(*) |
+------+------+------+----------+
| 3 | abc | 4 | 3 |
| 1 | def | 6 | 3 |
+------+------+------+----------+
2 rows in set (0.00 sec)
mysql> select /*+ SET_VAR(sql_mode=" ") */ a,b,c,count(*) from test_group_by group by b /*!99999 order by b */;
+------+------+------+----------+
| a | b | c | count(*) |
+------+------+------+----------+
| 1 | def | 6 | 3 |
| 3 | abc | 4 | 3 |
+------+------+------+----------+
2 rows in set (0.01 sec)
我这个 MySQL 是使用 8.0.29 的源码，修改版本号编译而来的。实验能证明对 /*!版本号 order by b */ 实际上是对我编译后的版本号生效，而不是原版本号。所以这里又增加了一个奇怪的知识点，就是修改版本号要注意，有可能引入坑，我这里就是一个坑。
参考:
https://dev.mysql.com/doc/refman/8.0/en/comments.html https://dev.mysql.com/doc/refman/8.0/en/extensions-to-ansi.html https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html
发表评论