MySQL表数据导入导出
发布时间：2022-10-06 11:10:25 所属栏目：MySql教程 来源：
导读： MySQL表数据导入导出
select into outfile和load data infile是MySQL中常用的表数据导出导入的方法。由于不能导出表结构信息MySQL 导出数据，因此一般不用于备份与恢复。
用法
---导出数
select into outfile和load data infile是MySQL中常用的表数据导出导入的方法。由于不能导出表结构信息MySQL 导出数据，因此一般不用于备份与恢复。
用法
---导出数
|
MySQL表数据导入导出 select into outfile和load data infile是MySQL中常用的表数据导出导入的方法。由于不能导出表结构信息MySQL 导出数据，因此一般不用于备份与恢复。 用法 ---导出数据 SELECT ... INTO OUTFILE 'file_name'; ---导入数据 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}] ...]; 注意事项 用户权限要求 导数的用户必须具有FILE权限。授予该权限的语句为： mysql> grant file on *.* to 'appuser'@'%'; secure_file_priv参数影响 该参数有以下三种情况： 需要注意的是，该参数为只读变量，无法动态修改。 mysql> set global secure_file_priv='/tmp'; ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable 只能通过修改配置文件后重启MySQL服务来改变。 [root@mysql-node1 ~]# vim /etc/my.cnf [mysqld] secure-file-priv='/tmp' [root@mysql-node1 ~]# systemctl restart mysqld [root@mysql-node1 ~]# mysql -uroot -p mysql> select @@secure_file_priv; +--------------------+ | @@secure_file_priv | +--------------------+ | /tmp/ | +--------------------+ 1 row in set (0.00 sec) local_infile参数影响 使用load data [local] infile导入数据时，如果外部数据在MySQL服务端，无需指定LOCAL关键字；反之，如果要导入的数据在客户端服务器上，则必须指定LOCAL关键字。 但是，使用LOCAL关键字还受到参数local_infile的影响。如果local_infile=0，则不允许使用LOCAL关键字从MySQL客户端导入数据。 mysql> select @@local_infile; +----------------+ | @@local_infile | +----------------+ | 0 | +----------------+ 1 row in set (0.00 sec) mysql> load data local infile '/tmp/players_0909.txt' into table players fields terminated by ','; ERROR 1148 (42000): The used command is not allowed with this MySQL version 幸运的是，该参数可以动态修改，无需重启MySQL服务。 mysql> set global local_infile=1; Query OK, 0 rows affected (0.00 sec) mysql> select @@local_infile; +----------------+ | @@local_infile | +----------------+ | 1 | +----------------+ 1 row in set (0.00 sec) 示例 mysql> select @@secure_file_priv; +--------------------+ | @@secure_file_priv | +--------------------+ | /tmp/ | +--------------------+ 1 row in set (0.00 sec) mysql> select * from players into outfile '/root/players_0909.txt' fields terminated by ','; ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement mysql> mysql> select * from players into outfile '/tmp/players_0909.txt' fields terminated by ','; Query OK, 6 rows affected (0.00 sec) ---truncate清空表的数据，但是会保留表结构 mysql> truncate table players; Query OK, 0 rows affected (0.00 sec) mysql> select * from players; Empty set (0.00 sec) mysql> load data infile '/tmp/players_0909.txt' into table players fields terminated by ','; Query OK, 6 rows affected (0.00 sec) Records: 6 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select count(*) from players; +----------+ | count(*) | +----------+ | 6 | +----------+ 1 row in set (0.01 sec) References。 （编辑：百客网 - 百科网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐