数据导入

数据导入

功能: 批量把数据存储到数据库的表里，把文件内容保存到表里。

格式：
load data infile "目录名/文件名"
into table 表名
fields terminated by "分隔符"
lines terminated by "\n";

注意：
字段分隔符要与文件内的一致
指定导入文件的绝对路径 (导入的文件要放在默认存储目录下/var/lib/mysql-files/)
导入数据的表字段类型要与文件字段类型一致 (有几列就要建几个字段)
禁用selinux保护机制
---------------------------------------------------------------------------------------------------------------------
将/etc/passwd文件导入userdb库user表并给每条记录加编号

mysql> use db3;
mysql> create table user(
-> name char(30),
-> password char(1),
-> uid int,
-> gid int,
-> comment char(150),
-> homedir char(150),
-> shell char(50)
-> );

mysql> show variables like "secure_file_priv"; 查看默认使用目录及目录是否存在
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

mysql> system cp /etc/passwd /var/lib/mysql-files/ 在命令的前面加上system,告诉系统这是系统命令,不是mysql的命令
mysql> system ls /var/lib/mysql-files
passwd

mysql> load data infile "/var/lib/mysql-files/passwd"
-> into table db3.user
-> fields terminated by ":" field(领域，场所),terminate(结束，终止)
-> lines terminated by "\n"; line(行)

mysql> select * from db3.user;

mysql> alter table user
-> add
-> id int primary key auto_increment first;

mysql> select * from db3.user where id=1; 看第1行
+----+------+----------+------+------+---------+---------+-----------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+------+----------+------+------+---------+---------+-----------+
| 1 | root | x | 0 | 0 | root | /root | /bin/bash |
+----+------+----------+------+------+---------+---------+-----------+

mysql> select * from db3.user where id<=10; 看前10行
+----+----------+----------+------+------+----------+-----------------+----------------+
| id | name | password | uid | gid | comment | homedir | shell |
+----+----------+----------+------+------+----------+-----------------+----------------+
| 1 | root | x | 0 | 0 | root | /root | /bin/bash |
| 2 | bin | x | 1 | 1 | bin | /bin | /sbin/nologin |
| 3 | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin |
| 4 | adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin |
| 5 | lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin |
| 6 | sync | x | 5 | 0 | sync | /sbin | /bin/sync |
| 7 | shutdown | x | 6 | 0 | shutdown | /sbin | /sbin/shutdown |
| 8 | halt | x | 7 | 0 | halt | /sbin | /sbin/halt |
| 9 | mail | x | 8 | 12 | mail | /var/spool/mail | /sbin/nologin |
| 10 | operator | x | 11 | 0 | operator | /root | /sbin/nologin |
+----+----------+----------+------+------+----------+-----------------+----------------+