MySQL对已存在数据库表添加自增ID字段

主要是遗留问题，该表本来只是用于分析，同事没有添加自增id，造成后续在处理时，遇到一些问题，需要对表新增一个自增的id字段（表中已经存在大量数据），以下是个人的解决方法。避免了开发代码处理。

outfile导出所有数据

select
replace(replace(replace(replace(id            , '\r\n', ''), '\n', ''), char("32"), ''), '^', ''),
replace(replace(replace(replace(uuid          , '\r\n', ''), '\n', ''), char("32"), ''), '^', ''),
replace(replace(replace(replace(title         , '\r\n', ''), '\n', ''), char("32"), ''), '^', ''),
replace(replace(replace(replace(link          , '\r\n', ''), '\n', ''), char("32"), ''), '^', ''),
replace(replace(replace(replace(intro         , '\r\n', ''), '\n', ''), char("32"), ''), '^', ''),
其他字段省略
)
from 库命.表名
where 1=1
-- limit 200
into outfile '/var/lib/mysql-files/all_202106.txt' 
fields terminated by '^' enclosed by '' 
lines terminated by '\r\n';

首先，将现有数据导出，上面使用了大量的replace，是因为里面的数据是爬虫所得，格式不统一，且存在大量的空格需要替换处理。这里字段分隔符为“^”。

sed每行行首添加空格

sed 's/^/""^&/g' all_202106.txt > all_20210602.txt

将上面导出的数据，通过sed命令，给每行行首添加一个空格，以“^”分割，和导出时分隔符保持一致。

LOAD DATA导入MySQL

LOAD DATA local INFILE '/var/lib/mysql-files/all_20210602.txt' IGNORE INTO TABLE 库命.表名  
CHARACTER SET utf8mb4 FIELDS TERMINATED BY '^' ENCLOSED BY '' LINES TERMINATED BY '\r\n' ignore 1 lines (
id           ,
uid           ,
uuid         ,
title        ,
link         ,
....
其他字段省略     
);

将修改好的数据直接再导入到新建的数据库表（设置新表ID自增），同时注意导入时设置字符编码格式为UTF8mb4，防止出现中文乱码的情况。

至此，对已存在数据库表添加自增ID操作完成。

 

注意：outfile时，如果提示无法导出，是因为数据库没有打开导出功能，或者导出路径没有添加。请自行百度解决。