建表导入csv

建表导入csv

部分代码参考

https://www.cnblogs.com/yoyotl/p/9858587.html

数据文件中的列数跟 MySQL 数据表字段数目没有完全匹配，并且 sql_mode 设为 strict 模式
先查询当前的sql_mode,再设置sql_mode的模式。

show variables like 'sql_mode';

set sql_mode='';

建表语句：

#①
create table `kcxxsj`(
`USER_KCDM` varchar(20) not null,
`KCMC` varchar(100) not null,
`jg_gh` int not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;




load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/cjsj.csv' 
into table kcxxsj character set gb2312 
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n'
ignore 1 lines;


#②
create table `cjsj`(
`KCMC` varchar(100) not null,
`USER_KCDM` varchar(20) not null,
`KSCJ` int not null,
`KHFS` varchar(10) not null,
`KCLB1` varchar(10),
`xh` int not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;




load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/cjsj.csv' 
into table cjsj character set gb2312 
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n'
ignore 1 lines;


#③
create table `jbxx`(
`XB` int not null,
`BJID` int not null,
`BJDM_USER` int not null,
`xh` int not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/jbxx.csv' 
into table jbxx character set gb2312 
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n'
ignore 1 lines;


#④
create table `zyxysj`(
`USER_DM` int not null,
`ZWMC` varchar(100) not null,
`SSYXB_USERDM` int not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/zyxysj.csv' 
into table zyxysj character set gb2312
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\r\n'
(`USER_DM`,  `ZWMC`, `SSYXB_USERDM` )