mysql相关笔记

创建唯一索引的字段，都不能允许为null，否则mysql的唯一性约束可能会失效。

 

SQL 中的三值逻辑，即真（TRUE）、假（FALSE）和未知（UNKNOWN）。
Null 与任何值都不相等，包括与另一个 Null 比较。

当你使用 NOT IN 条件时，如果其中包含 NULL 值，这会导致整个条件的结果不确定。具体来说：
如果一个值与 NULL 进行比较，结果是未知（UNKNOWN）。
如果一个条件的结果是未知（UNKNOWN），那么整个条件的结果也是未知（UNKNOWN）。

查询不出来数据
select * from book b where b.author not in('roger', NULL)
修改后的语句
select * from book b where b.author not in('roger') and b.author is not null

因此，当你使用 NOT IN 条件时，如果其中包含 NULL 值，它会导致整个条件结果为未知（UNKNOWN）。
在 SQL 中，任何未知（UNKNOWN）的条件都被视为不符合条件，因此相关的行将被过滤掉，不会包含在结果中。
这就是为什么在处理包含 NULL 值的情况时，需要谨慎地使用条件，以确保你的查询逻辑正确。

 

MYSQL索引失效
1 对索引使用左或者左右模糊匹配
select * from book b where name like '%张'
select * from book b where name like '%张%'

2 对索引使用函数
select * from book b where length(name) = 2
不过，从MySQL 8.0版本开始，数据库引入了函数索引这一特性，允许我们针对函数计算结果建立索引。
也就是说，索引的值可以是某个函数计算后的结果，这样就可以通过索引来快速查询所需数据。
ALTER TABLE book ADD KEY idx_name_length ((LENGTH(name)));

3 对索引进行表达式计算
explain select * from book b where id + 1 = 10;
然而，如果将查询条件修改为 WHERE id = 10 - 1，则可以利用索引进行查询。
explain select * from book b where id = 10 - 1;

4 对索引隐式类型转换
比如表里面有一个phone字段 是字符串类型的。如下这个语句可以查询出数据，但是不会使用到索引
explain select * from book b where phone = 1810XXX9208;

然而，如果索引字段是整型，即使查询条件中使用字符串类型的参数，索引依然能够正常生效。
例如，执行以下SQL：
explain select * from book b where id = '1';

为什么会出现上面的情况，MySQL的数据类型转换规则，它决定了在比较字符串和数字时，哪个会被转换。
select '10' > 9
可以看到结果是1。MySQL在比较时，会将字符串转换为数字。

5 联合索引非最左匹配
例如，对于 (a, b, c) 的联合索引 ，以下查询能够成功匹配联合索引：
WHERE a=3
WHERE a=3 AND b=5 AND c=4
WHERE a=3 AND b=5
然而，若查询条件不符合最左匹配原则，索引将失效，如以下查询：
WHERE b=5
WHERE c=4
WHERE b=5 AND c=4

6 WHERE 子句中的 OR
在 WHERE 子句中，如果 OR 前的条件是索引列而 OR 后的条件不是，索引也会失效。
解决办法：将OR后面的字段创建索引

 

NULL值引发的问题
1 COUNT/DISTINCT 数据丢失
SELECT COUNT(*) AS total_count, COUNT(name) AS name_count FROM person;
COUNT(name) 忽略了 name 字段为 NULL 的记录，导致统计结果丢失。

2 NULL 对比结果为未知（false）
在使用非等于查询（<> 或 !=）时，NULL 值的记录会被忽略。例如
SELECT * FROM person WHERE name != 'Java';
可以看到，name 为 NULL 的记录被忽略，导致查询结果不完整。
解决方案：
SELECT * FROM person WHERE name != 'Java' OR name IS NULL;

3 NULL 值运算都为 NULL
在使用 NULL 值进行运算时，比如加减乘除，拼接等等 最终的结果都为 NULL

4 聚合空指针异常
在使用聚合函数（如 SUM、AVG）时，如果字段值为 NULL，查询结果也会为 NULL，而不是预期的 0。
这可能导致程序在处理结果时出现空指针异常。
使用 IFNULL 函数：将 NULL 转换为 0，例如：
SELECT SUM(IFNULL(salary, 0)) AS total_salary FROM employee WHERE id >= 3;

5 Group By Order By 会统计 NULL 值