mysql mod MySQLmodel导出 |
您所在的位置：网站首页 › 中国的五位财神是谁呀 › mysql mod MySQLmodel导出 |
本篇来演示MySQL的导入和导出。 MySQL之导出 可以使用以下两种方法进行MySQL的导出，分别为： SELECT ... INTO OUTFILE ...Mysqldump工具1、SELECT ... INTO OUTFILE ... 1）语法如下： SELECT * FROM tablename INTO OUTFILE 'target_file' [option];其中option参数可以是以下选项： FIELDS TERMINATED BY 'string'（字段分隔符，默认为制表符‘\t’）；FIELDS [OPTIONALLY] ENCLOSED BY 'char' （字段引用符，如果加OPTIONALLY选项则只用在char、varchar和text等字符型字段上，默认不使用引用符）；FIELDS ESCAPED BY ‘char’ （转义字符，默认为‘\’）；LINES STARTING BY ‘string’ （每行前都加此字符串，默认‘’）；LINES TERMINATED BY ‘string’ （行结束符，默认为‘\n’）；2）使用默认选项导出数据库test下的t_mm表； mysql> select *from t_mm into outfile '/usr/local/mysql/mysql-files/emp.txt'; Query OK, 6 rows affected (0.04 sec) mysql> system more /usr/local/mysql/mysql-files/emp.txt; 1 Alen 2018-03-09 15:33:03 3 UK 2018-03-09 15:35:55 4 Japan 2018-03-09 12:36:16 6 USA 2018-03-09 13:19:35 8 Begin MMM.... 2018-03-09 14:54:40 10 Hello 2018-03-12 15:33:22 mysql>注：如果有同名的文件，则导出失败； 3）使用带选项的语句导出数据库test下的t_mm表； mysql> select *from t_mm into outfile '/usr/local/mysql/mysql-files/emp.txt' fields terminated by ',' enclosed by '"'; Query OK, 6 rows affected (0.00 sec) mysql> system more /usr/local/mysql/mysql-files/emp.txt; "1","Alen","2018-03-09 15:33:03" "3","UK","2018-03-09 15:35:55" "4","Japan","2018-03-09 12:36:16" "6","USA","2018-03-09 13:19:35" "8","Begin MMM....","2018-03-09 14:54:40" "10","Hello","2018-03-12 15:33:22" mysql>4）如上，第一列是数字类型，不希望用引号引起，语句可改为： mysql> select *from t_mm into outfile '/usr/local/mysql/mysql-files/emp.txt' fields terminated by ',' optionally enclosed by '"'; Query OK, 6 rows affected (0.00 sec) mysql> system more /usr/local/mysql/mysql-files/emp.txt; 1,"Alen","2018-03-09 15:33:03" 3,"UK","2018-03-09 15:35:55" 4,"Japan","2018-03-09 12:36:16" 6,"USA","2018-03-09 13:19:35" 8,"Begin MMM....","2018-03-09 14:54:40" 10,"Hello","2018-03-12 15:33:22" mysql>2、Mysqldump工具 1）语法如下： mysqldump -uusername -p -T target_dir dbname tablename [option]其中option参数可以是以下选项： --fields-terminated-by=name（字段分隔符）；--fields-enclosed-by=name（字段引用符）；--fields-optionally-enclosed-by=name（字段引用符，只用在char、varchar和text等字符型字段上）；--fields-escaped-by=name（转义字符）；--lines-terminated-by=name（记录结束符）；2）使用默认选项导出数据库test下的所有表； mysql> system mysqldump -uroot -p -T /usr/local/mysql/mysql-files/ test; Enter password: mysql> system ls -l /usr/local/mysql/mysql-files/ total 16 -rw-r--r--. 1 root root 1437 Mar 13 22:11 t_emp.sql -rw-rw-rw-. 1 mysql mysql 111 Mar 13 22:11 t_emp.txt -rw-r--r--. 1 root root 1435 Mar 13 22:11 t_mm.sql -rw-rw-rw-. 1 mysql mysql 171 Mar 13 22:11 t_mm.txt mysql> mysql> system more /usr/local/mysql/mysql-files/t_emp.txt 1 张三 2018-03-13 18:53:44 3 李四 2018-03-13 18:53:44 5 Alen 2018-03-13 17:37:34 7 USA 2018-03-13 17:39:45 mysql> system more /usr/local/mysql/mysql-files/t_emp.sql--导出表定义脚本3）使用带选项的语句导出数据库test下的t_emp表； mysql> system mysqldump -uroot -p -T /usr/local/mysql/mysql-files/ test t_emp --fields-terminated-by=',' --fields-optionally-enclosed-by='"'; Enter password: mysql> system ls -l /usr/local/mysql/mysql-files/ total 8 -rw-r--r--. 1 root root 1437 Mar 13 22:18 t_emp.sql -rw-rw-rw-. 1 mysql mysql 127 Mar 13 22:18 t_emp.txt mysql> system more /usr/local/mysql/mysql-files/t_emp.txt 1,"张三","2018-03-13 18:53:44" 3,"李四","2018-03-13 18:53:44" 5,"Alen","2018-03-13 17:37:34" 7,"USA","2018-03-13 17:39:45" mysql>以上，可以发现，mysqldump和select...into outfile...的选项和语法非常相似，其实，mysqldump实际调用的就是后者提供的接口，并在其上增添了新功能而已。 MySQL之导入 MySQL的导入功能和导出相对应，也有两种导入方式，分别为： LOAD DATA INFILE...MySQLImport工具1、LOAD DATA INFILE... 1）语法如下： LOAD DATA [LOCAL] INFILE 'filename' INTO TABLE tablename [option]其中option参数可以是以下选项： FIELDS TERMINATED BY 'string'（字段分隔符，默认为制表符‘\t’）；FIELDS [OPTIONALLY] ENCLOSED BY 'char' （字段引用符，如果加OPTIONALLY选项则只用在char、varchar和text等字符型字段上，默认不使用引用符）；FIELDS ESCAPED BY ‘char’ （转义字符，默认为‘\’）；LINES STARTING BY ‘string’ （每行前都加此字符串，默认‘’）；LINES TERMINATED BY ‘string’ （行结束符，默认为‘\n’）；IGNORE number LINES（忽略输入文件中的前n行数据）；（col_name_or_user_var，...）（按照列出的字段顺序和字段数量加载数据）；SET col_name=expr，...将列做一定的数值转换后再加载；2）加载导出的t_emp表数据； mysql> load data infile '/usr/local/mysql/mysql-files/t_emp.txt' into table t_emp fields terminated by ',' optionally enclosed by '"'; Query OK, 4 rows affected (0.02 sec) Records: 4 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select *from t_emp; +----+--------+---------------------+ | id | name | cdate | +----+--------+---------------------+ | 1 | 张三 | 2018-03-13 18:53:44 | | 3 | 李四 | 2018-03-13 18:53:44 | | 5 | Alen | 2018-03-13 17:37:34 | | 7 | USA | 2018-03-13 17:39:45 | +----+--------+---------------------+ 4 rows in set (0.00 sec) mysql>3）如果不希望加载前2行，可以用以下语句： mysql> truncate table t_emp; Query OK, 0 rows affected (0.06 sec) mysql> load data infile '/usr/local/mysql/mysql-files/t_emp.txt' into table t_emp fields terminated by ',' enclosed by '"' ignore 2 lines; Query OK, 2 rows affected (0.03 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select *from t_emp; +----+------+---------------------+ | id | name | cdate | +----+------+---------------------+ | 5 | Alen | 2018-03-13 17:37:34 | | 7 | USA | 2018-03-13 17:39:45 | +----+------+---------------------+ 2 rows in set (0.00 sec) mysql> system more /usr/local/mysql/mysql-files/t_emp.txt 1,"张三","2018-03-13 18:53:44" 3,"李四","2018-03-13 18:53:44" 5,"Alen","2018-03-13 17:37:34" 7,"USA","2018-03-13 17:39:45" mysql>4）使用LINES STARTING BY ‘string’选项过滤数据； mysql> system more /usr/local/mysql/mysql-files/t_emp.txt Alen1,"张三","2018-03-13 18:53:44" 3,"李四","2018-03-13 18:53:44" Alen5,"Alen","2018-03-13 17:37:34" 7,"USA","2018-03-13 17:39:45" mysql> load data infile '/usr/local/mysql/mysql-files/t_emp.txt' into table t_emp fields terminated by ',' enclosed by '"' lines starting by 'Alen'; Query OK, 2 rows affected (0.05 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select *from t_emp; +----+--------+---------------------+ | id | name | cdate | +----+--------+---------------------+ | 1 | 张三 | 2018-03-13 18:53:44 | | 5 | Alen | 2018-03-13 17:37:34 | +----+--------+---------------------+ 2 rows in set (0.00 sec) mysql>注：LINES STARTING BY ‘string’选项时，可跳过指定字符的前缀以及前缀之前的所有字符，如果某行不包含该指定字符，则该行记录也会被跳过。 5）表中的列数多于数据文件时的处理； mysql> desc t_emp; +-------+--------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+-------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | YES | | NULL | | | cdate | datetime | YES | | CURRENT_TIMESTAMP | | | sex | char(1) | YES | | NULL | | +-------+--------------+------+-----+-------------------+----------------+ 4 rows in set (0.00 sec) mysql> system more /usr/local/mysql/mysql-files/t_emp.txt 1,"张三","2018-03-13 18:53:44" 3,"李四","2018-03-13 18:53:44" 5,"Alen","2018-03-13 17:37:34" 7,"USA","2018-03-13 17:39:45" mysql> truncate table t_emp; Query OK, 0 rows affected (0.07 sec) mysql> load data infile '/usr/local/mysql/mysql-files/t_emp.txt' into table t_emp fields terminated by ',' enclosed by '"' (id,name,cdate); Query OK, 4 rows affected (0.06 sec) Records: 4 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select *from t_emp; +----+--------+---------------------+------+ | id | name | cdate | sex | +----+--------+---------------------+------+ | 1 | 张三 | 2018-03-13 18:53:44 | NULL | | 3 | 李四 | 2018-03-13 18:53:44 | NULL | | 5 | Alen | 2018-03-13 17:37:34 | NULL | | 7 | USA | 2018-03-13 17:39:45 | NULL | +----+--------+---------------------+------+ 4 rows in set (0.00 sec) mysql>6）表中的列数少于数据文件时的处理； mysql> desc t_emp; +-------+--------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+-------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | YES | | NULL | | | cdate | datetime | YES | | CURRENT_TIMESTAMP | | | sex | int(1) | YES | | NULL | | +-------+--------------+------+-----+-------------------+----------------+ 4 rows in set (0.00 sec) mysql> system more /usr/local/mysql/mysql-files/t_emp.txt 1,1,"张三","2018-03-13 18:53:44",25 3,1,"李四","2018-03-13 18:53:44",24 5,1,"Alen","2018-03-13 17:37:34",30 7,0,"Lucy","2018-03-13 17:39:45",20 mysql> truncate table t_emp; Query OK, 0 rows affected (0.07 sec) mysql> load data infile '/usr/local/mysql/mysql-files/t_emp.txt' into table t_emp fields terminated by ',' enclosed by '"' (id,sex,name,cdate,@tmp); Query OK, 4 rows affected (0.03 sec) Records: 4 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select *from t_emp; +----+--------+---------------------+------+ | id | name | cdate | sex | +----+--------+---------------------+------+ | 1 | 张三 | 2018-03-13 18:53:44 | 1 | | 3 | 李四 | 2018-03-13 18:53:44 | 1 | | 5 | Alen | 2018-03-13 17:37:34 | 1 | | 7 | Lucy | 2018-03-13 17:39:45 | 0 | +----+--------+---------------------+------+ 4 rows in set (0.00 sec) mysql>7）表中的列数和数据文件不一样，并且数据文件的列类型也不同时的处理； mysql> desc t_emp; +-------+--------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+-------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | YES | | NULL | | | cdate | datetime | YES | | CURRENT_TIMESTAMP | | | sex | int(1) | YES | | NULL | | +-------+--------------+------+-----+-------------------+----------------+ 4 rows in set (0.00 sec) mysql> system more /usr/local/mysql/mysql-files/t_emp.txt 1,"男","张三","2018-03-13 18:53:44",25 3,"男","李四","2018-03-13 18:53:44",24 5,"男","Alen","2018-03-13 17:37:34",30 7,"女","Lucy","2018-03-13 17:39:45",20 mysql> load data infile '/usr/local/mysql/mysql-files/t_emp.txt' into table t_emp -> fields terminated by ',' -> enclosed by '"' (id,@tmp_sex,name,cdate,@tmp) -> set sex=if(@tmp_sex='男',1,0); Query OK, 4 rows affected (1.05 sec) Records: 4 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select *from t_emp; +----+--------+---------------------+------+ | id | name | cdate | sex | +----+--------+---------------------+------+ | 1 | 张三 | 2018-03-13 18:53:44 | 1 | | 3 | 李四 | 2018-03-13 18:53:44 | 1 | | 5 | Alen | 2018-03-13 17:37:34 | 1 | | 7 | Lucy | 2018-03-13 17:39:45 | 0 | +----+--------+---------------------+------+ 4 rows in set (0.00 sec) mysql>2、MySQLImport工具 1）语法如下： mysqlimport -uroot -p [local] dbname tab.txt [option]其中option参数可以是以下选项： --fields-terminated-by=name（字段分隔符）；--fields-enclosed-by=name（字段引用符）；--fields-optionally-enclosed-by=name（字段引用符，只用在char、varchar和text等字符型字段上）；--fields-escaped-by=name（转义字符）；--lines-terminated-by=name（记录结束符）；--ignore-lines=number（忽略前几行）；注：mysqlimport命令实际上就是LOAD DATA INFILE语句的命令行调用接口，mysqlimport导入数据要求数据文件名与表名相同，更详细的命令可通过 mysqlimport --help查看。 2）用法和MySQLdump相同，举例省略...
|
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |
| CopyRight 2018-2019 实验室设备网 版权所有 win10的实时保护怎么永久关闭 |