网站短期技能培训,oa协同软件办公系统费用,.net美食网站开发源代码,如何介绍网站模板下载地址文章目录语法格式LOW_PRIORITYCONCURRENTLOCALREPLACE 或 IGNOREPARTITIONCHARACTER SET对导入文件的字段进行格式描述指定行的起始符号和终止符号IGNORE number LINES指定数据表的列名获取表达式的值存储到指定的列中开启/关闭功能参考示例示例一示例二示例三示例四示例五错误…
文章目录语法格式LOW_PRIORITYCONCURRENTLOCALREPLACE 或 IGNOREPARTITIONCHARACTER SET对导入文件的字段进行格式描述指定行的起始符号和终止符号IGNORE number LINES指定数据表的列名获取表达式的值存储到指定的列中开启/关闭功能参考示例示例一示例二示例三示例四示例五错误汇总secure_file_priv 参数的问题导入的记录包含的字段值个数超过要导入的列数导入文件的字符编码与数据表的字符编码不一致导入文件的字段数量与数据表的列数量不一致使用 local 与不使用 local 的差异语法格式
LOAD DATA[LOW_PRIORITY | CONCURRENT] [LOCAL]INFILE file_name[REPLACE | IGNORE]INTO TABLE tbl_name[PARTITION (partition_name [, partition_name] ...)][CHARACTER SET charset_name][{FIELDS | COLUMNS}[TERMINATED BY string][[OPTIONALLY] ENCLOSED BY char][ESCAPED BY char]][LINES[STARTING BY string][TERMINATED BY string]][IGNORE number {LINES | ROWS}][(col_name_or_user_var[, col_name_or_user_var] ...)][SET col_name{expr | DEFAULT}[, col_name{expr | DEFAULT}] ...]LOW_PRIORITY
如果你指定关键词LOW_PRIORITY,这个只对那些只采用了表级别锁如MYISAM)的引擎有影响比如InnoDB使用的是行锁不受这个影响具体的来说使用了LOW_PRIORITY则本操作会在其它线程完成之后再操作。
如果你指定关键词 low_priority那么 MySQL 将会等到没有其他人读这个表的时候才会插入数据。
CONCURRENT
CONCURRENT 会和其它线程同时进行这个对性能是有一些影响。
LOCAL
这是个非常重要的关键字指明了文件的位置简单的说如果指定了 local则表示文件位于客户端所在主机的位置如果没有指定则表示文件在数据库服务所在主机上即数据库服务器上。同时这个关键字的使用还会影响到 load data 命令对于错误数据的处理方式。
所谓客户端就是用来访问数据库和管理数据库的客户端软件例如 Navicat Premium 客户端。客户端是安装在用户自己的主机上通过客户端连接上远程数据库服务器后即登录数据库可以直接通过客户端的图形化界面对数据库进行管理也可以通过客户端的命令行界面对数据库发送命令。
例如下面的命令语句来导入数据
mysql load data low_priority local
infile /Users/liaowenxiong/Desktop/student.txt
into table student
columns terminated by ,
lines terminated by \n
ignore 1 lines
(id,age,name);
Query OK, 3 rows affected (0.10 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0注/Users/liaowenxiong/Desktop/student.txt 这个是文件在客户端所在主机的文件路径。
1.如果是指定了 local则数据从客户端读取文档中的说法是会在服务端的临时目录下创建一份文件的副本但我在测试的时候并没有发现。file_name 必须是文件在客户端所在主机的绝对路径例如/Users/liaowenxiong/Desktop/student.txt由于涉及到数据传输所以这种方式会相对来说慢一些。
我不使用 Navicat Premium 的命令终端使用本地主机的命令终端远程登录数据库后执行下面的命令
load data low_priority local
infile student.txt
into table student
columns terminated by ,
lines terminated by \n
ignore 1 lines
(id,age,name); 注这里指定了关键词 local直接使用文件名称。此时使用的是本地主机命令终端远程连接服务器所以这种情况下的本地其实就是服务器本地默认是在用户的家目录下查找文件当前我登录的是 root 用户那么就会在 /root/ 目录下查找文件。
2.如果未指定 local则文件应该是直接在服务端这种情况下如果文件名使用的是相对路径则又分两种情况一种是文件名前没有相对目录默认是在当前数据库对应的目录下查找。什么意思呢假设你当前使用的数据库名称叫 test那么在 MySQL 的数据根目录下就会有一个目录名称为 test那么命令就会在这个 test 目录下查找这个文件如果你在文件名称前面加上了目录名称例如ab/student.txt那么命令不会在 test 目录下查找但是依旧在 MySQL 的数据根目录下查找即会在 MySQL 数据根目录下查找文件路径 ab/student.txt。
如何查看 MySQL 数据库的数据根目录请参见《Linux 下如何查找 MySQL 数据库的数据根目录呢》
不指定关键词 local 直接使用文件名执行 load data infile 语句
mysql load data low_priority- infile student.txt- into table student- columns terminated by ,- lines terminated by \n - ignore 1 lines - (id,age,name);
ERROR 13 (HY000): Cant get stat of /var/lib/mysql/test/student.txt (Errcode: 2 - No such file or directory)我们看到提示无法获取文件 student.txt 的状态默认是在 /var/lib/mysql/test/ 目录查找文件其中 test 是当前数据库名称/var/lib/mysql 是 MySQL 的数据根目录。
不知道关键词 local 在文件名称前面再加上一个目录结果会如何
mysql load data low_priority- infile ab/student.txt- into table student- columns terminated by ,- lines terminated by \n - ignore 1 lines - (id,age,name);
ERROR 13 (HY000): Cant get stat of /var/lib/mysql/ab/student.txt (Errcode: 2 - No such file or directory)我们看到提示信息并没有在当前数据库下查找文件而是在数据根目录下根据你指定的相对路径查找文件。
3.如果指定了 local则当某条数据处理有误时系统把这个错误记录为一个 warning不会影响下一条数据的处理因为涉及到数据传输。而如果没有指定 local则默认情况下遇到错误后不会继续执行。
REPLACE 或 IGNORE
当前的数据跟表中的数据有惟一性冲突的时候采用什么样的方式是替换已有还是忽略当前。特别需要说明的是当这两种方式都未指定时如果数据来自于客户端则重复的数据会忽略如果来源于服务端则命令将终止执行。
服务端执行语句不指定 REPLACE 或 IGNORE
mysql load data low_priority- infile /root/student.txt- into table student- columns terminated by ,- lines terminated by \n - ignore 1 lines - (id,age,name);
ERROR 1062 (23000): Duplicate entry 00000000000000000001 for key PRIMARY服务端执行导入语句如果导入文档中存在与数据表主键重复的记录则直接报错。
客户端执行语句不指定 REPLACE 或 IGNORE
mysql load data low_priority local
infile /Users/liaowenxiong/Desktop/student.txt
into table student
columns terminated by ,
lines terminated by \n
ignore 1 lines
(id,age,name);
Query OK, 0 rows affected (0.08 sec)
Records: 3 Deleted: 0 Skipped: 3 Warnings: 3从上面执行结果看客户端执行导入语句后会把导入文档中与数据表主键重复的记录忽略即跳过。
PARTITION
指定具体的分区由于之前数据库中没用到过分区个人对这块也不熟悉所以暂时不解释等到了解了再补充
CHARACTER SET
指定导入文件的字符编码如果文件的字符编码和数据库的编码不一致可能会出现乱码的问题。所以要注意的是这里指定的是文件的字符编码而不是数据库的字符编码
对导入文件的字段进行格式描述 [{FIELDS | COLUMNS}[TERMINATED BY string][[OPTIONALLY] ENCLOSED BY char][ESCAPED BY char]]1.FIELDS 和 COLUMNS指定接下来描述的是导入文件中的字段或者列格式两个任选一个即可。 2.TERMINATED BY string 指定导入文件中的字段间分隔符是 ‘string’ 3.ENCLOSED BY char 指定字段值由什么符号包围 4.ESCAPED BY char 指定转义符号
注意在不指定这个参数的情况下默认的字段分隔符是水平制表符 \t默认字段值无任何值包围默认转义字符为 \。
指定行的起始符号和终止符号 [LINES [STARTING BY ‘string’] [TERMINATED BY ‘string’]]1.LINES 指定接下来描述的是导入文件中的行格式 2.STARTING BY string 指定行开始的符号 3.TERMINATED BY string 指定行结尾的符号
如果导入文件的数据记录没有起始符号而每行记录都是换行产生的那么这个选项可以省略。因为默认情况下行的起始符为空终止符为 \n对于 Windows 产生的文本文件来说需要指定换行符为 \r\n 。
IGNORE number LINES
忽略文件中的前 number 行通常情况下我们生成的文件可能有列名那么必须忽略列名即忽略文件的前 1 行。需要注意的是这里是行的数量而不是行号。
指定数据表的列名
有的时候我们不需要给数据表中的所有列都填充值这个时候就可以指定数据表中的列名用 () 将列名括起来。 就是将导入文件的字段依次序对应数据库表中列比方说导入文件中的第一个字段要存储到数据表的哪列中这个列名必须明确。
[(col_name_or_user_var,…)]注意 1.这里也可以是用户自定义的用户表达式 2.这里是指定数据库中表的列名不是导入文档中的列名
获取表达式的值存储到指定的列中
[SET col_name expr,…]如果在前一步中指定了用户表达式那么相应就可以使用列名等于用户表达式的方式来指定这个我没有用过给出一个官方的示例如下
LOAD DATA INFILE file.txt INTO TABLE t1(column1, var1) SET column2 var1/100;开启/关闭功能
LOAD DATA INFILE 这个导入数据命令语句是可以开启和关闭的必须开启此功能才能使用使用如下命令查看功能开启状态
mysql show global variables like local_infile;
----------------------
| Variable_name | Value |
----------------------
| local_infile | ON |
----------------------
1 row in set (0.01 sec)如果是关闭的则输入以下命令将其开启
set global local_infileON;参考示例
示例一
student.txt 的内容如下
id name
00000000000000000001 liaowenxiong
00000000000000000002 liudehua
00000000000000000003 zhangxueyoustudent.txt 的内容格式分析 1.第一行记录是列名称需要忽略掉 2.每列的值使用双引号括起来 3.字段值之间使用制表符分隔一种空白字符 4.每行记录以换行符结尾即以换行符分隔
数据表结构如下
mysql desc student;
------------------------------------------------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
------------------------------------------------------------------------------------------------------
| id | bigint(20) unsigned zerofill | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| score | double(4,1) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| insert_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
------------------------------------------------------------------------------------------------------
6 rows in set (0.00 sec)登录数据库后执行以下命令
mysql load data low_priority # 如果你指定关键词 low_priority那么 MySQL 将会等到没有其他人读这个表的时候才会插入数据
infile /root/student.txt # /root/student.txt 这是存放在数据库服务器上的导入数据的文本文件
into table student # 这是数据表
fields terminated by \t # 指定字段的分隔符用单引号括起来\t 表示tab字符也是默认的字段分隔符
enclosed by # 指定字段值的括起符号例如字段值使用双引号括起来那么就指定双引号注意单引号里面的双引号才是字段值的括起符号
escaped by \\ # 指定转义符号默认是反斜杠
lines terminated by \n # 指定每行记录的分隔符默认是换行符MacOS是\rLinux是\nWindows是\r\n
ignore 1 lines # 指定忽略前面的多少行记录如果导入数据的文本文件中含有列名称那么就忽略掉前面的第一行即可
(id,name); # 这里是指定数据表的字段名称表示文本文件的第一列对应数据表的id字段第二列对应数据表的name字段无注释版本
load data low_priority
infile /root/student.txt
into table student
fields terminated by \t
enclosed by
escaped by \\
lines terminated by \n
ignore 1 lines
(id,name); 示例二
student.txt 的内容如下
id name
00000000000000000001 liaowenxiong
00000000000000000002 liudehua
00000000000000000003 zhangxueyoustudent.txt 的内容格式分析 1.第一行记录是列名称 2.本示例中字段值没有使用双引号括起来所以不需要通过 enclosed by 来指定字段闭合符号。 3.字段值之间使用制表符分隔一种空白字符 4.每行记录以换行符结尾即以换行符分隔
执行以下命令即可导入数据
load data low_priority
infile /root/student.txt
into table student
fields terminated by \t
lines terminated by \n
ignore 1 lines
(id,name); 示例三
student.txt 的内容如下
id,age,name
00000000000000000001,18,liaowenxiong
00000000000000000002,28,liudehua
00000000000000000003,38,zhangxueyoustudent.txt 的内容格式分析 1.第一行记录是列名称需要忽略掉 2.本示例中字段值没有使用双引号括起来所以不需要通过 enclosed by 来指定字段闭合符号。 3.字段值之间使用逗号分隔 4.每行记录以换行符结尾即以换行符分隔 5.文档中的字段 age 和 name 与数据表的顺序不同
登录数据库后执行命令语句如下
load data low_priority
infile /root/student.txt
into table student
columns terminated by ,
lines terminated by \n
ignore 1 lines
(id,age,name); # 表示文档的第一列和数据表的id字段对应第二列和数据表的age字段对应第三列和数据表的name字段对应示例四
student.txt 的内容如下
id,age,name
00000000000000000001,18,liaowenxiong
00000000000000000002,28,liudehua
00000000000000000003,38,zhangxueyou通过客户端 Navicat Premium 的命令行终端执行以下的命令
load data low_priority local
infile /Users/liaowenxiong/Desktop/student.txt
into table student
columns terminated by ,
lines terminated by \n
ignore 1 lines
(id,age,name); 通过客户端的命令终端执行 load data infile 可以使用文件在本地的文件路径但是要在前面指定关键词 local。
示例五
student.txt 的内容如下
id,age,name
00000000000000000001 18 liaowenxiong
00000000000000000002 28 liudehua
00000000000000000003 38 zhangxueyou执行如下命令语句导入数据
load data low_priority
infile /root/student.txt
into table student
fields terminated by
lines terminated by \n
ignore 1 lines
(id,age,name); 特别注意字段之间如果使用【空格】分隔也就是按一下空格键那么这样的分隔符号用 表示单引号内输入一个空格即可不是 \t 也不是 \b。
错误汇总
secure_file_priv 参数的问题
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement在 MySQL 5.7.6 版本引入了参数 secure_file_priv该参数用来指定 LOAD DATA、SELECT … INTO OUTFILE 语句和 LOAD_FILE() 函数的导入导出目录。
secure_file_priv 的值如下
值作用nullMySQL 服务禁止导入和导出操作目录路径如果这个参数设为一个目录路径MySQL 服务只允许在这个目录中执行文件的导入和导出操作。这个目录必须存在MySQL 服务不会创建它。例如值为 /tmp/表示导入/导出只能发生在 /tmp/ 目录下没有值表示不对 mysqld 的导入/导出做限制
注默认值是 /var/lib/mysql-files/
查看参数 secure_file_priv 的值你可以输入
mysql show variables like %secure%;
---------------------------------
| Variable_name | Value |
---------------------------------
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | |
---------------------------------
3 rows in set (0.00 sec)如何解决 Windows下在 MySQL 安装目录下找到配置文件 my.ini打开文件后在其中的 [mysqld] 内加入 secure_file_priv Centos Linux 下修改 /etc/my.cnf 文件在 [mysqld] 内加入 secure_file_priv
然后重启 MySQL 配置才会生效。
因为 secure_file_priv 参数是只读参数不能使用 set global 命令修改
mysql set global secure_file_priv;
ERROR 1238 (HY000): Variable secure_file_priv is a read only variable导入的记录包含的字段值个数超过要导入的列数
ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns这个错误表示第一行被截取了第一行包含的数据超过了输入的列数输入的列数由后面的小括号指定(id,name)。产生这个问题的原因往往是因为报错的行被编辑过即第一行并没有在第二个字段的位置以换行符结尾而是后面还有字符可能是多余的空白字符等所以需要手动重新整理数据。
ERROR 1300 (HY000): Invalid utf8 character string: 导入文件的字符编码与数据表的字符编码不一致
ERROR 1300 (HY000): Invalid utf8 character string: 可以将导入文件的字符编码改成数据表的字符编码保存后再执行命令语句。
导入文件的字段数量与数据表的列数量不一致
ERROR 1261 (01000): Row 228 doesnt contain data for all columns可能是因为数据库的【严格转换模式】导致输入以下的命令查看数据库的参数 sql_mode 的值 mysql show variables like sql_mode;
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Variable_name | Value |
----------------------------------------------------------------------------------------------------------------------------------------------------------
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
----------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)将其中的 STRICT_TRANS_TABLES 就是严格转换模式可以将其去掉。
set sql_modeONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION;使用 local 与不使用 local 的差异
如果 load data 语句使用了 local 参数则客户端使用 TCP 远程连接数据库服务器时例如你在自己的电脑通过命令终端使用 SSH 连接远程数据库服务器在这种情况下所谓的本地local其实就是服务端主机了那么该命令就会在服务端主机上直接读取指定的导入文件没有 file 权限仍然能够导入文本文件这个时候是非常危险的因为 local 参数的内部原理是从客户端的主机读取文本文件并传送到服务端的 /tmp 目录并保存为一个临时文件再执行 load data 语句的。
另外要使用 local 参数还需要看服务端和客户端是否都开启了 local_infile 功能默认是开启的任何一端的 local_infile 关闭了都不能使用 local 参数会报如下的错误
ERROR 1148 (42000): The used command is not allowed with this MySQL version如果 load data 语句不使用 local 参数则用户必须要有导入文件的 file 权限才能够执行 load data 语句导入文本文件的数据到数据库中。如果没有file权限而执行 load data 语句导入文本文件会报没有 file 权限的错误或者报如下的错误
ERROR 1045 (28000): Access denied for user test% (using password: YES)为了安全要限制客户端使用 local 参数在没有 file 权限的时候使用 load data 语句导入文本文件数据需要在服务端将参数 local_infile 的值设为 OFF。
如果想全面禁止使用 load data 语句则可以将参数 secure_file_priv 的值设为 NULL这样就无法使用 load data infile 语句导入数据了也无法使用 select into outfile 语句导出数据。
