实验目的
将Windows下的excel表格及txt文本导入MySQL数据库
实验环境
RockyLinux9.2的系统，yum安装的MySQL8.0版本数据库
[root@localhost ~]# cat /etc/redhat-release Rocky Linux release 9.2 (Blue Onyx) [root@localhost ~]# mysql -V mysql Ver 8.0.32 for Linux on x86_64 (Source distribution)
1、把Excel表格文件另存为CSV
常规的Excel表格基本都是老版本xls或者新的xlsx格式，是不支持直接导入MySQL数据库的，需要先转换文件为CSV格式，也就是英文逗号分割格式，自己新建个txt文件然后用逗号分割改成csv也可以。
2、创建数据库（database）及表(table)
安装mysql并且设置密码
yum install mysql-server -y systemctl enable mysqld --now mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypasswd'; exit;
新建数据库，再新建一个和Excel表格一模一样列的数据库表，表格有几列就要在数据库对应
登录MySQL
mysql -u root -p
查看现有数据库情况
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec)
创建数据库，名称是ip
mysql> create database ip; Query OK, 1 row affected (0.01 sec)
切换到刚新建的ip这个数据库
mysql> use ip; Database changed
查看切换数据库是否成功，当前在哪个数据库下面
mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | ip | +------------+ 1 row in set (0.00 sec)
可以看到在ip数据库下，这里不得不表扬一下mariadb数据库，切换了数据库是会自己显示的，不需要这一步操作
创建一个表，名称是xuniji
mysql> CREATE TABLE xuniji ( IP地址 VARCHAR(255), 系统 VARCHAR(255), 用途 VARCHAR(255) ); Query OK, 0 rows affected (0.01 sec)
查看表情况
mysql> show tables; +--------------+ | Tables_in_ip | +--------------+ | xuniji | +--------------+ 1 row in set (0.00 sec)
描述表的内容
mysql> describe xuniji; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | IP地址 | varchar(255) | YES | | NULL | | | 系统 | varchar(255) | YES | | NULL | | | 用途 | varchar(255) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
表就创建好了，下一步就是导入Excel表格
3、导入
接上文，使用如下命令导入
LOAD DATA INFILE '/path/to/csv/file' INTO TABLE tablename FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
上述命令中，'/path/to/csv/file'是CSV文件的路径，tablename是你要导入数据的表名。FIELDS TERMINATED BY ','表示各列之间使用逗号分隔，LINES TERMINATED BY '\n'表示行末使用换行符。IGNORE 1 ROWS表示忽略文件中的首行，因为它包含列名而不是数据。
把Excel表格文件上传到mysql指定的文件夹/var/lib/mysql-files，不然会报错ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement，查看目录位置如下
mysql> show variables like '%secure%'; +--------------------------+-----------------------+ | Variable_name | Value | +--------------------------+-----------------------+ | require_secure_transport | OFF | | secure_file_priv | /var/lib/mysql-files/ | +--------------------------+-----------------------+ 2 rows in set (0.06 sec)
再次导入又报错了
mysql> LOAD DATA INFILE '/var/lib/mysql-files/IP.csv' INTO TABLE xuniji FIELDS TERMINATED BY ',' LINES TERRMINATED BY '\n' ; ERROR 13 (HY000): Can't get stat of '/var/lib/mysql-files/IP.csv' (OS errno 13 - Permission denied)
更改下Excel表格的文件权限
mysql> LOAD DATA INFILE '/var/lib/mysql-files/IP.csv' INTO TABLE xuniji FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS; ERROR 13 (HY000): Can't get stat of '/var/lib/mysql-files/IP.csv' (OS errno 13 - Permission denied)
又报错了，还是没权限
mysql> EXIT; Bye
关闭selinux
setenforce 0
再次导入依然报错，不过不是权限问题了
mysql> LOAD DATA INFILE '/var/lib/mysql-files/IP.csv' INTO TABLE xuniji FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS; ERROR 1366 (HY000): Incorrect string value: '\xD3\xC9\xC6\xF7\x0D' for column '用途' at row 10
好像是Excel表格第10行有问题，用vim编辑器打开发现中文全是乱码，因此将文件转换成utf-8
iconv -f GBK -t UTF-8 IP.csv > IP-utf8.csv
再次用vim编辑器打开发现文件没有乱码了
继续导入，终于成功了
mysql> LOAD DATA INFILE '/var/lib/mysql-files/IP-utf8.csv' INTO TABLE xuniji FIELDS TERMINATED BY ',' LINEES TERMINATED BY '\n' IGNORE 1 ROWS; Query OK, 36 rows affected (0.01 sec) Records: 36 Deleted: 0 Skipped: 0 Warnings: 0
4、验证
查询下第一列IP地址，检测下成果
mysql> SELECT ip地址 FROM xuniji; +-----------------+ | ip地址 | +-----------------+ | 10.10.10.1 | | 10.10.10.2 | | 10.10.10.3 | | 10.10.10.4 | | 10.10.10.5 |
后面还有很长，就不复制了，至此Excel表格导入MySQL数据库就圆满成功并结束了。可见虽然MySQL数据库和Excel表格有着天然的相似性，都是关系型表格呈现数据，但是想导入也绝非易事。
我这个测试表格没有任何公式和复杂的链接也只有3列，导入都花了这么多步骤，现实中的办公表格比这个复杂得多，困难也更加多。
1、新建TXT文本
首先在Windows电脑新建个test.txt文件，模拟学生信息表，编辑好了之后导入到MySQL数据库，txt文件的内容如下，上一个Excel表格是CSV格式，通过加逗号来来实现，其实导入数据库的文件也支持空格，只要能符合MySQL要求的格式就好了，在使用 LOAD DATA INFILE 命令时，列分隔符可以是任何字符，包括逗号、制表符、分号、空格等。它不必是英文的逗号。因此，如果您的文本文件中使用的是其他字符作为列分隔符，您可以在 LOAD DATA INFILE 命令中指定该字符作为分隔符。我的这个TXT文件是以一个空格作为分隔符。注意，我测试发现空格个数必须统一，比如一个空格那么整个TXT文本就都必须是一个空格，否则导入时就会报错导致导入失败。不得不说，MySQL对于数据文件的导入要求是非常严格的，不论是文件权限还是数据格式。既要保证安全性也要保证高效利用CPU、内存资源、尽可能少占用磁盘空间，所以创建表的时候甚至对于数据的长度都有规定。就像C语言，语法工整，定义要求严格，对于开发人员来说不友好，但是对于计算机来说正好相反，执行效率高可以以极低的硬件资源消耗来实现最佳的效果。所以单片机，硬件资源以MB甚至KB来记，却也能编程跑程序，那些高级语言比如Python对编程的人来说更加友好但却会消耗更多的硬件资源。下面这个是我的TXT文件
姓名 性别 年龄 电话号码
张三 男 15 12345
李四 男 20 88465
阿花 女 18 465414
二狗 男 22 26465
小美 女 30 556461
2、新建student数据库及info表
新建一个数据库，名称为student
mysql> create database student; Query OK, 1 row affected (0.01 sec)
查看数据库情况
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | ip | | mysql | | performance_schema | | student | | sys | +--------------------+ 6 rows in set (0.00 sec)
切换到student数据库
mysql> use student; Database changed
新建一个表，名称为info
mysql> CREATE TABLE info ( -> `姓名` VARCHAR(255), -> `性别` VARCHAR(16), -> `年龄` INT, -> `电话号码` VARCHAR(16)); Query OK, 0 rows affected (0.01 sec)
3、导入test.txt文件
把电脑上的test.txt文件上传到RockyLinux9.2系统的/var/lib/mysql-files目录下，上面导入Excel表格的时候发现MySQL定义的只有这个目录下的文件才能导入数据库。
查看表
mysql> show tables; +-------------------+ | Tables_in_student | +-------------------+ | info | +-------------------+ 1 row in set (0.00 sec)
描述表结构
mysql> describe info; +--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | 姓名 | varchar(255) | YES | | NULL | | | 性别 | varchar(16) | YES | | NULL | | | 年龄 | int | YES | | NULL | | | 电话号码 | varchar(16) | YES | | NULL | | +--------------+--------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)
导入TXT文件
mysql> LOAD DATA INFILE '/var/lib/mysql-files/test.txt' INTO TABLE info FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\n' IGNORE 1 ROWS; Query OK, 5 rows affected (0.00 sec) Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
已成功导入，上面的/var/lib/mysql-files/test.txt要写文件的绝对路径，FIELDS TERMINATED BY ' '单引号里面有一个空格，用于区分单元列，LINES TERMINATED BY '\n'表示一个换行符，具体地说，这个选项告诉MySQL在读取文本文件时，将文件中的每一行作为一个记录插入到表中，直到遇到一个换行符（\n），IGNORE 1 ROWS表示忽略第一行，我的这个TXT文件中第一行是定义列的名称，也就是table info。
4、验证成果
mysql> select * from info; +--------+--------+--------+--------------+ | 姓名 | 性别 | 年龄 | 电话号码 | +--------+--------+--------+--------------+ | | 男 | 15 | 12345 | | 男 | 20 | 88465 | | 女 | 18 | 465414 | | 男 | 22 | 26465 | | 女 | 30 | 556461 +--------+--------+--------+--------------+ 5 rows in set (0.00 sec)
可以看到将TXT文本导入电脑的实验也已经成功，当然也可以指定逗号，分号等作为每列的分隔符，只需要在导入文件进MySQL表的的时候告诉MySQL分隔符是什么就好了。
发表评论