Mysql 遇到的坑--总结 2021-01-16 记录 暂无评论 795 次阅读 ## Mysql 遇到的坑--总结 [TOC] ### 表属性设置为 Null 的问题 使用 Null 属性非常方便，SQL 语句或者代码不需要额外的的填充或判断。使用 Null 主要存在的隐形问题如下： #### 关于 Null 的长度并不是 0 ```sql select length('') , length(null), length(0), length('0') ```  #### 关于 Null 的查询 测试表为 +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | one | varchar(255) | NO | MUL | NULL | | | two | varchar(255) | YES | MUL | NULL | | | three | varchar(255) | YES | MUL | NULL | | +-------+--------------+------+-----+---------+----------------+ > CREATE TABLE `null_test` ( > `id` int(11) NOT NULL AUTO_INCREMENT, > `one` varchar(255) NOT NULL, > `two` varchar(255) DEFAULT NULL, > `three` varchar(255) DEFAULT NULL, > PRIMARY KEY (`id`), > KEY `one` (`one`), > KEY `two` (`two`), > KEY `three` (`three`) > ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; 测试数据内容 +----+-----+------+-------+ | id | one | two | three | +----+-----+------+-------+ | 1 | | a2 | a3 | | 2 | b1 | NULL | b3 | | 3 | c1 | c2 | NULL | | 4 | d1 | d2 | NULL | +----+-----+------+-------+ > -- ---------------------------- > > -- Records of null_test > > -- ---------------------------- > > INSERT INTO `null_test` VALUES ('1', '', 'a2', 'a3'); > INSERT INTO `null_test` VALUES ('2', 'b1', null, 'b3'); > INSERT INTO `null_test` VALUES ('3', 'c1', 'c2', null); > INSERT INTO `null_test` VALUES ('4', 'd1', 'd2', null); ```sql mysql> select * from null_test where two = null; Empty set (0.00 sec) mysql> select * from null_test where two != null; Empty set (0.00 sec) mysql> select * from null_test where two is not null; +----+-----+------+-------+ | id | one | two | three | +----+-----+------+-------+ | 1 | | a2 | a3 | | 3 | c1 | c2 | NULL | | 4 | d1 | d2 | NULL | +----+-----+------+-------+ 3 rows in set (0.00 sec) mysql> select * from null_test where two != 'zzz'; +----+-----+------+-------+ | id | one | two | three | +----+-----+------+-------+ | 1 | | a2 | a3 | | 3 | c1 | c2 | NULL | | 4 | d1 | d2 | NULL | +----+-----+------+-------+ 3 rows in set (0.00 sec) ``` > - 查询 null 不能直接使用 `=` > - != 'zzz' 和 is not null 的结果一样 #### 关于 Null 对索引的影响 如果属性使用了 Null，需要找到为空的数据，只能只是用 is null 来查找，is not null 不会走索引。 ``` mysql> explain select * from null_test where two is null\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: null_test partitions: NULL type: ref possible_keys: two key: two key_len: 768 ref: const rows: 1 filtered: 100.00 Extra: Using index condition 1 row in set, 1 warning (0.00 sec) mysql> explain select * from null_test where two is not null\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: null_test partitions: NULL type: ALL possible_keys: two key: NULL key_len: NULL ref: NULL rows: 4 filtered: 75.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) ``` 所以，不建议使用 null 来当查找对象，使用空字符串或0来代替 #### Null 的计算 null 和 任何数字计算都是null； ``` mysql> select 1 + null; +----------+ | 1 + null | +----------+ | NULL | +----------+ 1 row in set (0.00 sec) mysql> select concat( "1" + null); +---------------------+ | concat( "1" + null) | +---------------------+ | NULL | +---------------------+ 1 row in set (0.00 sec) ``` #### null 参与聚合函数 使用原有测试数据，共有 4 条数据 ``` mysql> select count(*) from null_test; +----------+ | count(*) | +----------+ | 4 | +----------+ 1 row in set (0.00 sec) mysql> select count(three) from null_test; +--------------+ | count(three) | +--------------+ | 2 | +--------------+ 1 row in set (0.00 sec) mysql> select count(two) from null_test; +------------+ | count(two) | +------------+ | 3 | +------------+ 1 row in set (0.00 sec) ``` 使用 count 来计算的行数是不为 null 的行，这里需要多加注意。 PS：结合以上的所有问题，这里建议实际开发中不要使用 null 作为默认值，现在目前已经存在的表，可以改为空字符串或0代替，避免开发中很多不必要的麻烦。 ### 索引的问题 #### 字符串类型在查询时没有使用引号，不会使用表索引 ``` mysql> explain select * from null_test where one = 1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: null_test partitions: NULL type: ALL possible_keys: one key: NULL key_len: NULL ref: NULL rows: 4 filtered: 25.00 Extra: Using where 1 row in set, 3 warnings (0.00 sec) mysql> explain select * from null_test where one = '1'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: null_test partitions: NULL type: ref possible_keys: one key: one key_len: 767 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) mysql> ``` #### where条件左边的字段参与了函数或数学运算，不会使用表索引 ``` mysql> explain select * from null_test where concat(one, 'liurui') = '1'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: null_test partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) ``` #### 联合索引最左前缀不匹配，不会使用表索引 ### 事务出错的问题 - 数据库锁的分类 - 按照锁数据的粒度去分 - 行级锁 - 表级锁 - 按照数据的锁定方式去分 - 乐观锁 - 悲观锁 - 排它锁 for update - 共享锁 lock in share mode; 这里有个常见的坑，如果查询或修改的行没有加索引，innodb的行级锁会升级为表级锁。 打赏: 微信, 支付宝 标签: java, mysql 本作品采用 知识共享署名-相同方式共享 4.0 国际许可协议 进行许可。
微信
支付宝