我正在参加「掘金·启航计划」

前言

近期在做新旧系统数据迁移割接，使用的是MySql5.7版本，写了大量的存储过程进行数据操作，所以也就有了此文，跟大家分享分享一些我踩过的那些坑，废话少说直入正题。

踩坑就绪

语法校验sql_mode

sql_mode指的是MySQL在运行前会先进行一轮语法检测，检测到语法有误的sql直接抛出异常，下面我们就看个栗子。

-- 创建一个测试表
DROP TABLE IF EXISTS test_sql_mode;
CREATE TABLE `test_sql_mode`(  
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `type` CHAR(1) NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  `price` NUMERIC(10) NOT NULL,
  `create_time` DATETIME,
  PRIMARY KEY (`id`)
);
-- 初始化几条数据
INSERT INTO test_sql_mode(`type`, `name`, `price`, `create_time`)
VALUES
('1', '比亚迪-宋', 150000, NOW()),
('1', '比亚迪-汉', 200000, NOW()),
('1', '比亚迪-唐', 100000, NOW()),
('2', '丰田-凯美瑞', 200000, NOW()),
('2', '丰田-汉兰达', 300000, NOW());

由上面的结果我们查询得出以下结果。

在这里我们要说的问题是在进行group by操作时，按照我们合规的语法当中如有要进行group by操作，那么要查询的结果字段就需要进行group by或者使用到聚合函数，否则那就是语句不合法。又下图可见我们对type字段进行group by操作，而同时要查询出name以及price字段，那么没有使用聚会函数那就会出现以下错误。

上面的问题该如何处理，首先我们可以先查看一下当前mysql所配置的sql_mode，以及如何修改sql_mode。

-- 查看当前session的sql_mode
SELECT @@sql_mode;
-- 查看全局配置的sql_mode
SELECT @@global.sql_mode;
-- 查询得出的结果 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

-- 我们把上面得到的结果修改去除ONLY_FULL_GROUP_BY，重新设置当前session的sql_mode
SET sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
-- 重新设置全局的sql_mode
SET @@global.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

以上sql_mode修改完之后我们重新执行刚才group by的SQL语句如下：

也就是说sql_mode是用于限制我们sql语句的编写语法的，还有其他的限制有兴趣的话可自行琢磨。

日期数据类型timestamp

一般比较常用的日期类型就是timestamp和datetime。其中timestamp占4个字节，以utc的格式储存会进行时区检索；datetime占8个字节，不进行时区检索。

-- 创建一个测试表
CREATE TABLE `test_date`(  
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `birthday` TIMESTAMP,
  `create_time` DATETIME,
  PRIMARY KEY (`id`)
);

如上图，空表直接查询我们可以看出birthday默认是取当前时间，而datetime则默认NULL（注：建表语句为设默认值），也就是说timestamp是强制不为空，如果为空就取当前时间，如果不确定可执行下面语句确认。

INSERT INTO test_date(birthday, create_time) VALUES(NULL, NULL);

timestamp也就是时间戳，且timestamp有最大时间限制，且最大时间为2038-01-19 11:14:07，下图我们尝试超过1秒就出现错误。（也不知道大量使用timestamp的系统过了这个时间不知道会怎样😊）

综上所述，timestamp存储空间占用小，涉及跨时区的系统会自动进行时区检索，由于是用4个字节存储，所以不足的地方可能就是只能存储到最大时间2038-01-19 11:14:07。

delete语句索引丢失或检索效率下降

直接上测试数据

-- 创建一个测试表，将user_id添加到唯一索引
DROP TABLE IF EXISTS `test_index`;
CREATE TABLE `test_index`(  
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT NOT NULL,
  `user_name` VARCHAR(50) NOT NULL,
  `create_time` DATETIME,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `user_idx` (`user_id`)
);

INSERT INTO test_index(`user_id`, `user_name`, `create_time`)
VALUES
(1, '张三', NOW()),
(2, '李四', NOW()),
(3, '王五', NOW());

在此我们主要根据mysql执行计划explain中的type连接类型来初步判断sql的性能，在这里仅简单用于测试讲解不做大数据测试。

根据上面的测试数据，我们简单根据user_id做一个查询，大家应该都知道覆盖的是唯一索引那么执行计划中的type应该就是const。

相同的sql语句我们直接把select *替换成delete再来看一下有没有出乎你们的意料。

有没有发现，可以说是完全相同的sql，只不过一个是select一个是delete操作，而连接类型之间从const降为range进行范围检索，可想而知如果是大量数据的话那这个效率是要大打折扣的，那么我们就来看看下图sql优化后的结果如何。

如上图我们简单的对表加了一个别名，结果就跟我们的select操作完全一样，连接类型又变更为const级别。原因是因为mysql在执行select语句的时候会自动会sql进行优化，而delete则反而被优化的不够全面。那为什么在这里我们仅仅加了一个别名又可以走索引了呢？其实是加了别名后mysql就采用了LooseScan策略进一步做了优化。LooseScan是semi join子查询的一种执行策略。

存储过程游标遍历中断

这个问题我开始也很懵逼，以前有写存储过程都是在oracle，所以这次在mysql上写存储过程就撞墙了。这个问题有点难以描述，简单建两个表先上代码再解释。

-- 创建客户主表
DROP TABLE IF EXISTS cust_test;
CREATE TABLE `cust_test` (  
  `id` BIGINT(12) NOT NULL AUTO_INCREMENT,
  `cust_id` BIGINT(12) NOT NULL,
  `type` CHAR(1) NOT NULL,
  PRIMARY KEY (`id`) 
) ENGINE=INNODB CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO cust_test(cust_id, `type`)
SELECT 1 AS cust_id, '1' AS `type` FROM DUAL UNION ALL
SELECT 2 AS cust_id, '1' AS `type` FROM DUAL UNION ALL
SELECT 3 AS cust_id, '1' AS `type` FROM DUAL UNION ALL
SELECT 4 AS cust_id, '2' AS `type` FROM DUAL UNION ALL
SELECT 5 AS cust_id, '2' AS `type` FROM DUAL UNION ALL
SELECT 6 AS cust_id, '2' AS `type` FROM DUAL;

-- select * from cust_test;

DROP TABLE IF EXISTS cust_info_test;
CREATE TABLE `cust_info_test` (  
  `id` BIGINT(12) NOT NULL AUTO_INCREMENT,
  `cust_id` BIGINT(12) NOT NULL,
  `name` VARCHAR(20) NOT NULL,
  `age` INT NOT NULL,
  PRIMARY KEY (`id`) 
) ENGINE=INNODB CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO cust_info_test(cust_id, `name`, age)
SELECT 1 AS cust_id, '张三1' AS `name`, 19 AS age FROM DUAL UNION ALL
SELECT 2 AS cust_id, '张三2' AS `name`, 18 AS age FROM DUAL UNION ALL
SELECT 4 AS cust_id, '张三4' AS `name`, 18 AS age FROM DUAL UNION ALL
SELECT 5 AS cust_id, '张三5' AS `name`, 20 AS age FROM DUAL;

根据以上的测试数据，我们定义一个存储过程如下：

SET @result:='result:';
-- 测试存储过程
DROP PROCEDURE IF EXISTS test_temp;
DELIMITER $$
CREATE PROCEDURE test_temp()
BEGIN
	DECLARE done BOOLEAN DEFAULT FALSE;
	DECLARE custid BIGINT(12);
	DECLARE custName VARCHAR(20);
        -- 对cust_test表进行遍历
	DECLARE temp_cursor CURSOR FOR SELECT cust_id FROM cust_test;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=TRUE;
	-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
	-- DECLARE CONTINUE HANDLER FOR 1327 SET done = true;
	
	OPEN temp_cursor;
	REPEAT FETCH temp_cursor INTO custid;
		IF !done THEN
                        -- 查找有cust_info_test数据的cust_name
			SELECT `name` INTO custName FROM cust_info_test WHERE cust_id = custid LIMIT 1;
			SET @result:= CONCAT(@result, custName, ',');
		END IF;
	UNTIL done END REPEAT;
	CLOSE temp_cursor;
END $$
DELIMITER ;

CALL test_temp();
SELECT @result;

根据以上的数据，不难发现只有1、2、4、5四条cust_test数据在表cust_info_test有关联，那么存储过程执行完成，@result的结果猜测应该是result:张三1,张三2,张三4,张三5，接下来我们测试运行结果如下：

很显然跟我们猜测的结果不一样，那到底是怎么回事？

原因是因为我们在SELECT name INTO custName FROM cust_info_test WHERE cust_id = custid LIMIT 1;该语句中进行into操作，而当游标遍历到cust_id=3的时候结果为空，所以这里就会直接导致异常从而使SQLSTATE='02000'退出循环，而输出两个张三2是因为第一个正常，第二次查找cust_id=3为空custName取值不变也就是上一轮循环的张三2，那么该如何解决这种问题？

将以上存储过程的循环体优化如下：

-- 以上增加遍历 declare custCount int;
IF !done THEN
        SELECT COUNT(*), `name` INTO custCount, custName FROM cust_info_test WHERE cust_id = custid LIMIT 1;
        -- 空值不进行输出
        IF custName IS NOT NULL THEN
                SET @result:= CONCAT(@result, custName, ',');
        END IF;
END IF;

由以上修改完就达到了我们预期的目的，结果如下：

总结

宝剑锋从磨砺出,梅花香自苦寒来，还是得多磨练才知道原来事情没那么简单啊。