Mysql日常使用注意事项(”坑“) , 官方文档的解读

 

 

 

第一个 SQL DDL 为什么创建，修改表异常。

场景1，建表。

CREATE TABLE t_1 (
id INT(10) NOT NULL AUTO_INCREMENT,
name VARCHAR(65535) COLLATE utf8_bin DEFAULT '',
value VARCHAR(10) COLLATE utf8_bin NOT NULL COMMENT '',
age INT(10) NOT NULL DEFAULT 0,
PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='用户表'

报错：Column length too BIG ..... name(max = "21845") 列太大了？

 

太长那我name 改成小于21845个字符的 21840个 试试？

CREATE TABLE t_1 (
id INT(10) NOT NULL AUTO_INCREMENT,
name VARCHAR(21840) COLLATE utf8_bin DEFAULT '',
value VARCHAR(10) COLLATE utf8_bin NOT NULL COMMENT '',
age INT(10) NOT NULL DEFAULT 0,
PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='用户表'

继续报错：Row size too Large ..... maxmun row size is "65535"（not counting BLOBS）？ 行数据太大了？？？

 

太长那我列数据再小点？ 改成小于10000 个字符试试？

CREATE TABLE t_1 (
id INT(10) NOT NULL AUTO_INCREMENT,
name VARCHAR(10000) COLLATE utf8_bin DEFAULT '',
value VARCHAR(10) COLLATE utf8_bin NOT NULL COMMENT '',
age INT(10) NOT NULL DEFAULT 0,
PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='用户表'

OK 可以了 。。。

 

场景2，可能这时我们业务变动了，改表加字段。。 （忽略Online DDl）

ALTER TABLE t_11
ADD COLUMN key VARCHAR(66535) DEFAULT '' NOT NULL ;

 

。。同样的错误.....

那么，WHY ？

这里查下官方文档的描述吧 。
https://dev.mysql.com/doc/search/?d=201&p=1&q=1118

官方文档里说到了两个限制：
1- 列数的限制 ：
MySQL对每个表有4096列的硬限制的前提下：
（InnoDB 变长列的页外存储可以避免 InnoDB行大小限制）
还有额外限制：行数据大小的限制，存储格式和字符集等因素，存储引擎的限制（InnoDB 1017列），索引等虚拟列的限制


2- 每行数据大小的限制 :
MySQL对行有 65,535字节的最大行限制的前提下
(1)InnoDB对页限制 https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html 。
- 表最多1017列
- 表最多64个 二级索引。
- 多列索引最多允许16列。
- 最大的行小于页面的一半。例如，默认的最大innodb_page_size16KB行大小 约为8000个字节。
那么我查询下我的Mysql的页是多大？

show VARIABLES like "innodb_page_size";
innodb_page_size 16384 16KB

(2)不同的存储格式使用不同数量的页面标题和尾部数据

扩展 - MySQL InnoDB 逻辑存储结构

MYSQL的文件空间管理
https://dev.mysql.com/doc/refman/8.0/en/innodb-file-space.html

总的来说 ： 每个表的列数，行大小是有限制的，

---

第二个 SQL select * from is null DML

CREATE TABLE t_2 (
id int(11) DEFAULT NULL,
d_time datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB

 

insert into t_2 (id) select 1;
insert into t_2 select 2, now();
insert into t_2 (id) select 3;

然后我查询：

SELECT * from t_2 WHERE d_time is null ;

为什么我查询的是NULL 他返回我的是 这个"0000-00..."?

这里再查下官方文档。
https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_is-null

 

解释一下：就是官方说这个查询的条件列中已经声明了这个数据是 not null 的字段列，查询时时会查到这个”0000“的，原因是这个ODBC。

这个SQL在昨天讲的时候，有同学问更改、插入NULL会有什么问题呢？

然后写这个文档时我试了下：
先查看下当前Mysql 的 SQL_MODE的格式是什么？
公司测试库：

insert into t_2 select 4, NULL ;

竟然成了？

 

 

为啥？

官文：https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

||>show VARIABLES like 'sql_mode';
sql_mode NO_ENGINE_SUBSTITUTION

好吧，因为不是一个严格的 SQL_MODE ! 我自己设置成严格级别。
（ 注意这块使用的会话级别的，千万不要用全局级别的，如果MYSQL重启会变成全局的 ）

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
show VARIABLES like 'sql_mode';

再插入。报错了。。。
insert into t_2 select 6, NULL ;

 

 

 

---

第三个 SQL DQL order by limit 分页不对。

CREATE TABLE t_3 (
id INT(10) NOT NULL AUTO_INCREMENT,
name VARCHAR(300) COLLATE utf8_bin DEFAULT '',
value VARCHAR(10) COLLATE utf8_bin NOT NULL COMMENT '',
age INT(10) NOT NULL DEFAULT 0,
PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='用户表'

插入一批数据。

INSERT into t_3 (name,value,age) VALUES ("张三","java",10);
INSERT into t_3 (name,value,age) VALUES ("张三","php",12);
INSERT into t_3 (name,value,age) VALUES ("李四","java",10);
INSERT into t_3 (name,value,age) VALUES ("王五","php",10);

注意这里的age没有索引。 数据是这样的 ： SELECT * FROM t_3 ORDER BY age

 

然后我做个分页

第一页
SELECT * FROM t_3 ORDER BY age limit 0, 2;

第二页
SELECT * FROM t_3 ORDER BY age limit 2, 2;

为什么ID=4 出现了两次？ 看下执行计划

EXPLAIN SELECT id ,age FROM t_311 ORDER BY age limit 0 , 2;

Using filesort ? 就是说他是内存排序的？每次排序都会重新加载到内存？所以造成的问题吗？

加索引”index_age“。

ALTER TABLE t_3 ADD INDEX index_age ( age ) ;
再次查询：
SELECT * FROM t_3 ORDER BY age limit 0, 2;
SELECT * FROM t_3 ORDER BY age limit 2, 2;
无效。还是分错页！

再次查看执行计划：

那么到底应该怎么查分页啊？ 如果我们不需要全字段，那么可以这样。

SELECT id, age FROM t_3 ORDER BY age limit 2, 2;
再次查看执行计划：

既用上了索引，有用上了索引覆盖。

需要全字段，那么可以这样。

SELECT * FROM t_3 FORCE INDEX(index_age) ORDER BY age limit 0, 2;

参考官方文档： https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html

第四个 SQL DML update set and 。

update a set a = a and b=b where ?

利用上个SQL的数据。更新一个值试试。
SELECT * FROM t_3 ;

update t_3 SET age = 8 and name= 'js' WHERE id = 1;
SELECT * FROM t_3 ;

WHY ？ value= ”js“为啥没更新？

官文： https://dev.mysql.com/doc/refman/8.0/en/update.html

查看官方文档可以知道 需要用”逗号“分开多个

如果是and 就变成的判断语句 ：
update t_3 SET age = （8 and name= 'js' WHERE id = 1 ） ;

---

第五个 SQL 有索引页update有可能锁全表。

CREATE TABLE t_5 (
c1 int(10) unsigned NOT NULL DEFAULT '0',
c2 int(10) unsigned NOT NULL DEFAULT '0',
c3 int(10) unsigned NOT NULL DEFAULT '0',
c4 int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (c1),
KEY c2 (c2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入实验数据

INSERT into t_5 (c1,c2,c3,c4) VALUES (0,0,0,0);
INSERT into t_5 (c1,c2,c3,c4) VALUES (1,1,1,0);
INSERT into t_5 (c1,c2,c3,c4) VALUES (3,3,3,0);
INSERT into t_5 (c1,c2,c3,c4) VALUES (4,2,2,0);
INSERT into t_5 (c1,c2,c3,c4) VALUES (6,8,5,0);
INSERT into t_5 (c1,c2,c3,c4) VALUES (7,6,6,10);
INSERT into t_5 (c1,c2,c3,c4) VALUES (10,10,4,0);

注意 SESSION-2 查询的c2=1 ,SESSION-1 查询的是 c2 >= 6； 也就是加锁在 （6 , 12]

执行顺序	SESSION-1	SESSION-2	其他描述
1	BEGIN;	2
2	UPDATE t_5 SET c4 = 101 WHERE c2 >= 6 and c2 <= 12 ;
3		SELECT * from t_5 WHERE c2=1 FOR UPDATE; 【await...】	SESSION-2 一直等待中
4	COMMIT;
5		SELECT do.
6

查询当前的事务： SELECT * FROM information_schema.INNODB_TRX

查看当前的锁等待：SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

执行顺序	SESSION-1	SESSION-2	其他描述
1	BEGIN;	2
2	UPDATE t_51 SET c4 = 101 WHERE c2 >= 8 and c2 <= 12 ;
3		SELECT * from t_5 WHERE c2=1 FOR UPDATE;	可以查询没阻塞。
4
5
6	COMMIT;

就是说如果索引的区分度很低时，一个查询覆盖
https://dev.mysql.com/doc/refman/8.0/en/where-optimization.html

---

第六个 SQL

SELECT * FROM t_1 AS of 

这个SQL的坑刚刚是在公司MYSQL升级8.0时候遇到的，之前有个很老项目一直跑的好好的，升级8.0之后竟然出现了问题。
复用上个SQL的数据：

在我自己的测试库中运行：

SELECT VERSION();
SELECT * from t_31 as of ;

公司的测试库中执行

SELECT VERSION();
SELECT * from t_31 as of ;

WHY 查询官方文档

https://dev.mysql.com/doc/refman/8.0/en/keywords.html

 

这个 ”of“ 在之前都不是一个关键词，所以如果在写SQL的时候遇到别名时要想着是否是个别名，以及这个名词是不是未来可能会成为别名或者是关键词， 比如”of“这个别名在项目里写SQL的时候完全可以这么这 ”o_f“ 或者 ”of_“ 至少这样在mysql 以后升级少了很多不必要的麻烦。

 

未完，待续。

 

参考：
https://imysql.com/2020/07/13/why-the-not-nullable-column-with-is-null-condition-can-find-rows.shtml
https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html
https://time.geekbang.org/column/article/82865