Linux下MySQL导入excel文件

首先将excel文件导出为utf-8格式的csv文件(excel里面不需要表头,只需要数据就行),

然后用notepad++打开后,选择"编码"选项中"使用ANSI编码"后保存上传至服务器.

一般安装的MySQL编码格式都是UTF-8,可通过show variables like 'char%';查看.若和下图一致,则不需要设置:

否则请参照https://www.jianshu.com/p/2724f3e61c4d设置编码后

创建数据库→创建表(一定要设置主键primary key):

create table shop_product (id int primary key auto_increment,
												typename varchar(30),
												name varchar(30),
												price double(5,2),
                           photos varchar(255));

若创建表时失误删除表时报错可参照如下解决:

一般会遇到文件权限的问题,报错如下:

"ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement"

此时可通过show variables like "secure_file_priv";查看可导入mysql的安全文件位置.一般为"/var/lib/mysql-files/",可将文件复制到此目录下.

然后导入数据

load data infile "/var/lib/mysql-files/3.csv" into table school_area character set utf8 fields terminated by "," lines terminated by '\r\n';