第一步:改格式
将xlsx格式改成csv格式
第二步:查看信息
在linux下查看改为完格式的表信息
[root@zhu ~]# cat '/root/桌面/zhy(复件).csv'
1,a,1
2,b,2
3,c,3
4,d,4
5,e,5
6,f,6
7,g,7
8,h,8
9,j,9
10,k,12
第三步:导入数据
[root@zhu ~]# mysql -uroot -p123456 ----登入到数据库
mysql> show variables like "secure_file_priv"; ----查看导入数据可以存放的位置
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)
mysql> create table yan(id int(2),name char(3),age int(2)); -----创建可以导入的表
Query OK, 0 rows affected (0.24 sec)
mysql> system cp /root/桌面/zhy(复件).csv /var/lib/mysql-files/ ------改变导入的表格权限,使得mysql可以读取
mysql> system chmod o+r /var/lib/mysql-files/zhy(复件).csv
mysql> system ls -ld /var/lib/mysql-files/zhy(复件).csv
-rw----r--. 1 root root 72 7月 31 09:32 /var/lib/mysql-files/bb.txt
mysql> load data infile "/var/lib/mysql-files/zhy(复件).csv" into table yan fields terminated by ","; -----导入数据
Query OK, 10 rows affected (0.04 sec)
Records: 10 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from yan;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | a | 1 |
| 2 | b | 2 |
| 3 | c | 3 |
| 4 | d | 4 |
| 5 | e | 5 |
| 6 | f | 6 |
| 7 | g | 7 |
| 8 | h | 8 |
| 9 | j | 9 |
| 10 | k | 12 |
+------+------+------+
10 rows in set (0.00 sec)