背景:
使用MYSQL时经常面临到需要将EXCEL导入到数据库的问题。当我们在图形用户界面SQLyog选中数据库的一个表格,然后右键选择导入时,会出现下面三个格式。其中第一个“导入外部数据”的功能只能时充值成为企业旗舰版才可以使用。那么我们如何免费向数据库导入EXCEL表格呢?
我们想到的时第二个功能,即导入本地加载的CSV数据。
一、local_infile变量的设置
首先在SQLyog输入下面的命令,查找全局变量‘local_infile’是否已经打开。
show variables like 'local_infile';
如果上述全局变量的值为“OFF”,那么在导入CSV时会出现下面的报错。
这时,输入下面的命令,更改‘local_infile’为‘ON’。
set global 'local_infile'=ON;
二、数据源处理的必要性
1.选择导入格式为任意类型会出现的问题
如果没有对原数据格式设置,直接在SQLyog中选中需要导入新数据的数据表,右键选择“导入”,选择“使用本地加载的CSV数据”。我们可以点击下面右边的选项,发现可以导入非CSV格式。
选择导入全部文件类型,然后选中“xlsx”格式的目标excel上传,则默认的上传设置变成下面所示,默认字符集为“UTF-8”格式。但是window常用字符集为“gbk”,会出现字符集不符的报错。
我们可能会想到去更改字符集设置为“jbk”。这时不会出现上述的字符集的错误,但会出现导入数据后,在数据库里显示为乱码的问题。
2.直接修改源文件后缀名为“CSV”会出现的问题
通过上述我们可能想到还是传CSV文件,但是这里也容易出现一个问题。我们通常简单的方法就是直接修改源文件后缀名为“csv“。
传入到数据库后,会出现和上述第一个情况一样的报错,即字符集不符。
更改字符集设置为“jbk”后不会出现上述的字符集的错误,但会出现导入数据后,在数据库里显示为乱码的问题。原因是实际上还是和原数据表格式不匹配。(excel的字符集应为“UTF-8mb3“,这个格式在SQLyog的选项好像并没有)
上述表明,直接修改后缀名为“CSV“和选择文件类型为所有基本上没有差别。
3.对源文件格式的处理
这里的操作方法不是直接修改源文件名,而是将“xlsx“格式的源文件打开,然后另存为”UTF-8 COMMA DELIMITED”的格式。
然后再次导入另外保存的CSV格式的文件,如下面所示。这时字符集会显示为“default“,即为符合
最后就成功导入了EXCEL到sql数据库。