MySQL实战问题总结

一.基础问题

1.1 删除语句

1.1.1 delete

• delete是DML，事务提交之后才会生效；
• 执行delete操作时，每次从表中删除一行，并且将该行的删除操作记录在redo和undo日志中以便进行回滚和重做操作；
• 删除表中的数据，不删除表的结构，不释放空间；
• 删除大数据量的表速度会很慢；

1.1.2 truncate

• truncate是DDL，即操作会立即生效，不能回滚；
• 删除表中的数据，不删除表结构，释放空间；
• 对于外键约束引用的表，不能使用truncate table(会报错：Cannot truncate a table referenced in a foreign key constraint)；也不能使用drop table(会报错：Cannot delete or update a parent row:a foreign key constraint fails)；应该使用不带where 子句的delete语句；

1.1.3 drop

• drop是DDL，即操作会立即生效，不能回滚；
• 删除数据，删除表结构，释放空间；同时还会删除表结构所依赖的约束、触发器、索引，依赖于该表的存储过程/函数会保留，变成invalid状态；

1.1.4 总结

1. 表和索引所占空间
   truncate：表和索引所占空间恢复为初始大小；
   delete：不会减少表或索引所占用的空间；
   drop：将表所占用空间全部释放掉；
2. truncate和不带where的delete
   truncate：速度快，效率高；通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放；
   delete：每删除一行，都在事务日志中为所删除的每行记录一项。

1.2 timestamp

问题的起源是：工作里发现有的表会将时间字段设置为timestamp，有的会设置为timestamp(3)，突然发现对这块的内容有点生疏了，在MySQL8.0做了点测试。

-- 新建表
CREATE TABLE emp (   
 emp_id INT,  
 emp_name VARCHAR(20),   
 birthday timestamp);


--插入两条数据
insert into emp values(1,'11',current_timestamp);
insert into emp values(2,'11',current_timestamp);

1. 默认

-- 默认，时间到s
alter table emp modify birthday timestamp

2. timestamp(3)

-- 默认，时间到ms
 alter table emp modify birthday timestamp(3);

3. timestamp(6)

-- 默认，时间到us
alter table emp modify birthday timestamp(6);

4. timestamp(7)

 alter table emp modify birthday timestamp(7);

1.3 batchUpdate()

批量更新（新增）方法返回一个整形数组，名字、代表数字和含义如下：

名字	代表数字	含义
CLOSE_CURRENT_RESULT	1	调用 getMoreResults 时应该关闭当前 ResultSet 对象
KEEP_CURRENT_RESULT	2	保持当前resultSet
CLOSE_ALL_RESULTS	3	调用 getMoreResults 时应该关闭以前一直打开的所有 ResultSet 对象
SUCCESS_NO_INFO	-2	该常量指示批量语句执行成功但不存在受影响的可用行数计数
EXECUTED_FAILED	-3	执行失败
RETURN_GENERATED_KEYS	1	该常量指示生成的键应该可用于检索
NO_GENERATED_KEYS	2	该常量指示生成的键应该不可用于检索

有的时候需要确定批量更新是否全部成功，如：输入inputList的数量为num1，更新成功数量为num2（初始化为0），执行批量更新后得到int[] res，遍历res，如果res[i] != -3，num2 ++，然后比较num1是否等于num2。

二.进阶问题

2.1 索引问题

2.1.1 字符集不一致导致联表查询索引失效

彻底搞清楚MySQL字符编码不一致造成索引失效的问题

三.MySQL8新特性

3.1 GROUP BY

在MySQL8.0版本中，默认开启了严格模式，可以通过以下语句开启：

SET sql_mode = 'ONLY_FULL_GROUP_BY';

严格模式的特殊之处：

1. select后的字段除聚合字段外，必须出现在GROUP BY后；反过来，group by后的字段不一定出现在select后。
2. 在GROUP BY严格模式下，查询结果中的每一行都必须是一个组的聚合结果，而不是单个行的值。

起因：在工作中有这样一个场景，在表中每个cltNbr下可能存在多条数据

SELECT clt_nbr AS cltNbr
  , MIN(rcrd_id) AS rcrdId
FROM 
 ...
GROUP BY cltNbr
ORDER BY rcrc_id ASC

在上述sql语句中，因为GROUP BY在严格模式下，查询结果必须是一个组的聚合结果，所以按照rcrd_id进行排序，就不知道要取哪一个。将rcrd_id字段改为rcrdId即可。