天下事有难易乎?为之,则难者亦易矣;不为,则易者亦难矣。

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

往事如烟 1262次浏览 0个评论

点击上方“终码一生”,选择“加为星标”

第一时间获取技术干货!

系统环境:Ubuntu

数据库:MySQL5.7


主要是遗留问题,该表本来只是用于分析,同事没有添加自增id,造成后续在处理时,遇到一些问题,权衡之后,决定对表新增一个自增的id字段(表中已经存在大量数据,非业务表),为了节省时间,以下是个人的解决方法。避免了代码的开发。方法思路供参考!

1、outfile导出所有数据

select
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,是因为里面的数据是爬虫所得,格式不统一,且文本中存在大量的空格,跨行等特殊符号需要替换处理,同时为了避免文本中存在和分隔符相同的符号,我们也将其替换为空。导出数据,这里字段分隔符为“^”。

2、sed每行行首添加空字符

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

将上面导出的数据,通过sed命令,给每行行首添加一个空字符,并以“^”分割,和导出时数据分隔符保持一致。后面通过设置数据库ID为自增,为每个ID生成唯一标识。

3、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           ,
title ,
link         ,
....
其他字段省略
);

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

至此,对已存在数据库表添加自增ID操作完成。导出,添加行首空字符,再导入MySQL一共花费3个小时左右时间,基本都花费在导出和导入。

注意:outfile时,如果提示无法导出,是因为数据库outfile导出功能没有开启,或者导出路径没有添加。可以调整secure-file-priv参数的值,具体请自行百度解决。

PS:防止找不到本篇文章,可以收藏点赞,方便翻阅查找哦。

 

转自公号:终码一生

链接:https://mp.weixin.qq.com/s/PIPXXvjcQRukaipoBquGuQ


ITZOO版权所有丨如未注明 , 均为原创丨转载请注明来自IT乐园 ->MySQL 对已存在数据表添加自增 ID 字段
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址