mysql 竖线分割字段转换列数据列数缺少问题

竖线分割用的是 mysql.help_topic 表，最大上限是 637 条。这个不满足项目需求

1. 问题

• 表

  
  sBook.png

• 语句分割（637条）

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(a.sBook,'|',b.help_topic_id+1),'|',-1) AS sBook FROM `TbBookList`  a JOIN mysql.help_topic b ON b.help_topic_id < (LENGTH(a.sBook) - LENGTH(REPLACE(a.sBook,'|',''))+1) AND a.pkCode = '001I' ORDER BY a.sBook

语句分割.png

• 实际数量

  
  实际数量.png

2. 原因

• help_topic本身是Mysql一个帮助解释注释表，用于解释Mysql各种专有名词，由于这张表数据ID是从0顺序增加的，方便我们用于计数，但是8.0.17版本的只有686条数据，超过这个数字，我们就需要己自定义一张表
  可以用做计数的临时表，查询的语句只会用help_topic计数，超出的部分其实都是脏数据

• 我的数据库版本是（5.7.21），只有 637 条。

  
  数据库版本.png
• 如果过长的字符串，可能需要借助其他自增的辅助表（可以是现有表，也可以自己造一个 1,2,3,4 递增的行即可）

3. 解决

• 创建一个辅助表 help_topic

  
  辅助表.png

• 写一个存储过程(插入十万条数据)

DELIMITER //
CREATE PROCEDURE insert_data()  
BEGIN                    
DECLARE i INT DEFAULT 0; 
WHILE i<100000              
DO            
INSERT INTO `help_topic` (NAME) VALUES ('forbidden_delete');  
SET i=i+1;         
END WHILE ;      
COMMIT;        
END //
DELIMITER;    

存储过程.png

• 调用存储过程

 CALL insert_data();  

调用存储过程.png 结果.png

• 问题解决了？突然发现结果少了一位

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(a.sBook,'|',b.help_topic_id+1),'|',-1) AS sBook FROM `TbBookList`  a JOIN help_topic b ON b.help_topic_id < (LENGTH(a.sBook) - LENGTH(REPLACE(a.sBook,'|',''))+1) AND a.pkCode = '001I' ORDER BY a.sBook

• 结果 651 条

  
  最新结果.png

• 实际 652 条

  
  实际最新结果.png

• 少了 000003 这个结果，肯定是语句有问题

4. 问题分析

• 这个时候，我们要去找原来那个系统表了，人家 id 是从0 开始 ，我们后建立的表是从 1 开始 的（粗心啊）！

  
  原表.png
• 按这个思路，给我们的表加上 0 ，删除存储过程,修改一下存储过程

DELIMITER //
CREATE PROCEDURE insert_data()  
BEGIN                    
DECLARE i INT DEFAULT 0; 
WHILE i<10000              
DO            
INSERT INTO `help_topic` (help_topic_id,NAME) VALUES (i,'forbidden');  
SET i=i+1;         
END WHILE ;      
COMMIT;        
END //
DELIMITER;  

• 删除表数据，重置自动增长列 (不要自增，直接插入)

DELETE FROM help_topic;
TRUNCATE  help_topic ;