数据格式:邮箱1----密码1----用户名----证件号----密码2----电话----邮箱2
问题一:load data The used command is not allowed with this MySQL version
服务器端,local_infile默认开启;客户端,local_infile默认关闭,因此用的时候需要打开。
mysql> show global varaiables like ‘local_infile’;
mysql>set global local_infile = ‘ON’;
问题二:ERROR 1300 (HY000): Invalid utf8mb4 character string: ‘\05A3\04BB’
起初以为是字符串编码问题,把数据库、表、字段都改为utf8,问题依然存在
从网上了解到是文件钟有特殊字符 “\” 将之删除即可。
问题三:错误代码: 1467 Failed to read auto-increment value from storage engine
删除表-》重新建表-》 设置自增起始值
DROP TABLE IF EXISTS tablename
;
CREATE TABLE tablename
(
id
INT AUTO_INCREMENT,
email1
VARCHAR(50) ,
password1
VARCHAR(128) ,
realname
VARCHAR(20) ,
idcard
VARCHAR(20) ,
password2
VARCHAR(128) ,
phone
VARCHAR(20) ,
email2
VARCHAR(50) ,
PRIMARY KEY (id
)
) ENGINE=INNODB DEFAULT CHARSET=utf8
ALTER TABLE tablename
AUTO_INCREMENT =1
问题三网友解决办法参考
以上是格式化数据遇到的问题
附上添加格式化数据到数据库的MySql指令:
mysql>LOAD DATA LOW_PRIORITY LOCAL INFILE ‘D:/formatdata.txt’
INTO TABLEtablename
FIELDS TERMINATED BY ‘----’
ENCLOSED BY ‘’
LINES TERMINATED BY ‘\n’
IGNORE 5 LINES
(email1,password1,realname,idcard,password2,phone,email2)
C:\Users\AN>mysqlimport -u root -p123456 -P3306 cninfo “D:\Program Files\BaiduNetdisk\BaiduNetdiskDownload\bak12306.txt” --fields-terminated-by="----" --fields-enclosed-by="" --lines-terminated-by="\n" --ignore-lines=5 --columns=email1,password1,realname,idcard,password2,phone,email2
MySQL version:
C:\Users\AN>mysql -V
mysql Ver 8.0.16-commercial for Win64 on x86_64 (MySQL Enterprise Server - Commercial)
具体意思请参考**文章**