安全的网站建设服务,千锋培训,手机网站建设公司,为什么做网站都用phpMySQL导入/导出数据 文章目录 MySQL导入/导出数据一、MySQL 导入数据1、mysql 命令导入2、source 命令导入3、使用 LOAD DATA 导入数据4、使用 mysqlimport 导入数据4.1、mysqlimport的常用选项介绍 二、MySQL 导出数据1、使用 SELECT ... INTO OUTFILE 语句导出数据2、mysqldu…MySQL导入/导出数据 文章目录 MySQL导入/导出数据一、MySQL 导入数据1、mysql 命令导入2、source 命令导入3、使用 LOAD DATA 导入数据4、使用 mysqlimport 导入数据4.1、mysqlimport的常用选项介绍 二、MySQL 导出数据1、使用 SELECT ... INTO OUTFILE 语句导出数据2、mysqldump 导出表作为原始数据2.1、mysqldump 实例1. 导出整个数据库2. 导出特定表3. 导出数据库结构4. 导出压缩文件5.导出 SQL 格式的数据 3、将数据表及数据库拷贝至其他主机 一、MySQL 导入数据
1、mysql 命令导入
使用 mysql 命令导入语法格式为
mysql -u your_username -p -h your_host -P your_port -D your_databaseyour_username、your_host、your_port、your_database 分别为你的 MySQL 用户名、主机、端口和数据库。
实例
# mysql -uroot -p123456 runoob.sql以上命令将将备份的整个数据库 runoob.sql 导入。
执行上述命令后系统将要求输入 MySQL 用户的密码。输入密码并按Enter键。
这样MySQL 将执行 SQL 文件中的语句将数据导入到指定的数据库中。
请注意如果 SQL 文件包含创建数据库的语句确保在执行导入之前数据库已经存在。如果文件包含创建表的语句确保表不存在或者是空的以免导入数据时发生冲突。
2、source 命令导入
source 命令导入数据库需要先登录到数库终端
mysql create database abc; # 创建数据库
mysql use abc; # 使用已创建的数据库
mysql set names utf8; # 设置编码
mysql source /home/abc/abc.sql # 导入备份数据库使用 source 命令的好处是你可以在 MySQL 命令行中直接执行而无需退出 MySQL 并使用其他命令。
3、使用 LOAD DATA 导入数据
MySQL 中提供了LOAD DATA INFILE语句来插入数据。 以下实例中将从当前目录中读取文件 dump.txt 将该文件中的数据插入到当前数据库的 mytbl 表中。
mysql LOAD DATA LOCAL INFILE dump.txt INTO TABLE mytbl;如果指定LOCAL关键词则表明从客户主机上按路径读取文件。如果没有指定则文件在服务器上按路径读取文件。
你能明确地在LOAD DATA语句中指出列值的分隔符和行尾标记但是默认标记是定位符和换行符。
两个命令的 FIELDS 和 LINES 子句的语法是一样的。两个子句都是可选的但是如果两个同时被指定FIELDS 子句必须出现在 LINES 子句之前。
如果用户指定一个 FIELDS 子句它的子句 TERMINATED BY、[OPTIONALLY] ENCLOSED BY 和 ESCAPED BY) 也是可选的不过用户必须至少指定它们中的一个。
mysql LOAD DATA LOCAL INFILE dump.txt INTO TABLE mytbl- FIELDS TERMINATED BY :- LINES TERMINATED BY \r\n;LOAD DATA 默认情况下是按照数据文件中列的顺序插入数据的如果数据文件中的列与插入表中的列不一致则需要指定列的顺序。
如在数据文件中的列顺序是 a,b,c但在插入表的列顺序为b,c,a则数据导入语法如下
mysql LOAD DATA LOCAL INFILE dump.txt - INTO TABLE mytbl (b, c, a);4、使用 mysqlimport 导入数据
mysqlimport 客户端提供了 LOAD DATA INFILEQL 语句的一个命令行接口。mysqlimport 的大多数选项直接对应 LOAD DATA INFILE 子句。
从文件 dump.txt 中将数据导入到 mytbl 数据表中, 可以使用以下命令
$ mysqlimport -u root -p --local mytbl dump.txt
password *****mysqlimport 命令可以指定选项来设置指定格式,命令语句格式如下
$ mysqlimport -u root -p --local --fields-terminated-by: \--lines-terminated-by\r\n mytbl dump.txt
password *****mysqlimport 语句中使用 --columns 选项来设置列的顺序
$ mysqlimport -u root -p --local --columnsb,c,a \mytbl dump.txt
password *****4.1、mysqlimport的常用选项介绍
选项功能-d or --delete新数据导入数据表中之前删除数据数据表中的所有信息-f or --force不管是否遇到错误mysqlimport将强制继续插入数据-i or --ignoremysqlimport跳过或者忽略那些有相同唯一 关键字的行 导入文件中的数据将被忽略。-l or -lock-tables数据被插入之前锁住表这样就防止了 你在更新数据库时用户的查询和更新受到影响。-r or -replace这个选项与i选项的作用相反此选项将替代 表中有相同唯一关键字的记录。–fields-enclosed- by char指定文本文件中数据的记录时以什么括起的 很多情况下 数据以双引号括起。 默认的情况下数据是没有被字符括起的。–fields-terminated- bychar指定各个数据的值之间的分隔符在句号分隔的文件中 分隔符是句号。您可以用此选项指定数据之间的分隔符。 默认的分隔符是跳格符Tab–lines-terminated- bystr此选项指定文本文件中行与行之间数据的分隔字符串 或者字符。 默认的情况下mysqlimport以newline为行分隔符。 您可以选择用一个字符串来替代一个单个的字符 一个新行或者一个回车。
mysqlimport 命令常用的选项还有 -v 显示版本version -p 提示输入密码password等。
二、MySQL 导出数据
MySQL 中可以使用 SELECT…INTO OUTFILE 语句来简单的导出数据到文本文件上。
1、使用 SELECT … INTO OUTFILE 语句导出数据
SELECT…INTO OUTFILE 是 MySQL 中用于将查询结果导出到文件的语法。
SELECT…INTO OUTFILE 允许你将查询的结果写入一个文本文件基本的使用方法
SELECT column1, column2, ...
INTO OUTFILE file_path
FROM your_table
WHERE your_conditions;参数说明
column1, column2, ...: 要选择的列。file_path: 指定输出文件的路径和名称。your_table: 要查询的表。your_conditions: 查询条件。
以下是一个简单的实例
SELECT id, name, email
INTO OUTFILE /tmp/user_data.csv
FIELDS TERMINATED BY ,
LINES TERMINATED BY \n
FROM users;在以上 SQL 语句中我们从 users 表中选择了 id、name 和 email 列并将结果写入了 /tmp/user_data.csv 文件。FIELDS TERMINATED BY ‘,’ 指定了列之间的分隔符逗号LINES TERMINATED BY ‘\n’ 指定了行之间的分隔符换行符。
需要注意的是执行 SELECT…INTO OUTFILE 需要相应的权限并且输出文件的目录需要是 MySQL 服务器可以写入的地方。
以下实例中我们将数据表 runoob_tbl 数据导出到 /tmp/runoob.txt 文件中:
mysql SELECT * FROM runoob_tbl - INTO OUTFILE /tmp/runoob.txt;你可以通过命令选项来设置数据输出的指定格式以下实例为导出 CSV 格式
mysql SELECT * FROM passwd INTO OUTFILE /tmp/runoob.txt- FIELDS TERMINATED BY , ENCLOSED BY - LINES TERMINATED BY \r\n;在下面的例子中生成一个文件各值用逗号隔开。这种格式可以被许多程序使用。
SELECT a,b,ab INTO OUTFILE /tmp/result.text
FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY
LINES TERMINATED BY \n
FROM test_table;SELECT … INTO OUTFILE 语句有以下属性:
LOAD DATA INFILE是SELECT … INTO OUTFILE的逆操作SELECT句法。为了将一个数据库的数据写入一个文件使用SELECT … INTO OUTFILE为了将文件读回数据库使用LOAD DATA INFILE。SELECT…INTO OUTFILE file_name’形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上因此您必须拥有FILE权限才能使用此语法。输出不能是一个已存在的文件。防止文件数据被篡改。你需要有一个登陆服务器的账号来检索文件。否则 SELECT … INTO OUTFILE 不会起任何作用。在UNIX中该文件被创建后是可读的权限由MySQL服务器所拥有。这意味着虽然你就可以读取该文件但可能无法将其删除。
2、mysqldump 导出表作为原始数据
mysqldump 是 MySQL 提供的用于备份和导出数据库的命令行工具。
mysqldump 是 mysql 用于转存储数据库的实用程序。它主要产生一个 SQL 脚本其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。
使用 mysqldump 导出数据需要使用 –tab 选项来指定导出文件指定的目录该目标必须是可写的。
mysqldump 基本的用法
mysqldump -u username -p password -h hostname database_name output_file.sql参数说明
-u: 指定 MySQL 用户名。-p: 提示输入密码。-h: 指定 MySQL 主机名。database_name: 要导出的数据库名称。output_file.sql: 导出数据保存到的文件。
以下实例将数据表 runoob_tbl 导出到 /tmp 目录中
$ mysqldump -u root -p --no-create-info \--tab/tmp RUNOOB runoob_tbl
password ******2.1、mysqldump 实例
1. 导出整个数据库
导出 mydatabase 数据库到 mydatabase_backup.sql 文件
mysqldump -u root -p mydatabase mydatabase_backup.sql2. 导出特定表
如果你只想导出数据库中的某个表可以使用以下命令
mysqldump -u username -p password -h hostname database_name table_name output_file.sql或
mysqldump -u root -p mydatabase mytable mytable_backup.sql3. 导出数据库结构
如果只想导出数据库结构而不包括数据可以使用 –no-data 选项
mysqldump -u username -p password -h hostname --no-data database_name output_file.sql4. 导出压缩文件
你可以将导出的数据进行压缩以减小文件大小。例如使用 gzip
mysqldump -u username -p password -h hostname database_name | gzip output_file.sql.gz5.导出 SQL 格式的数据
导出 SQL 格式的数据到指定文件如下所示
$ mysqldump -u root -p RUNOOB runoob_tbl dump.txt
password ******以上命令创建的文件内容如下
-- MySQL dump 8.23
--
-- Host: localhost Database: RUNOOB
---------------------------------------------------------
-- Server version 3.23.58--
-- Table structure for table runoob_tbl
--CREATE TABLE runoob_tbl (runoob_id int(11) NOT NULL auto_increment,runoob_title varchar(100) NOT NULL default ,runoob_author varchar(40) NOT NULL default ,submission_date date default NULL,PRIMARY KEY (runoob_id),UNIQUE KEY AUTHOR_INDEX (runoob_author)
) TYPEMyISAM;--
-- Dumping data for table runoob_tbl
--INSERT INTO runoob_tbl VALUES (1,Learn PHP,John Poul,2007-05-24);
INSERT INTO runoob_tbl VALUES (2,Learn MySQL,Abdul S,2007-05-24);
INSERT INTO runoob_tbl VALUES (3,JAVA Tutorial,Sanjay,2007-05-06);如果你需要导出整个数据库的数据可以使用以下命令
$ mysqldump -u root -p RUNOOB database_dump.txt
password ******如果需要备份所有数据库可以使用以下命令
$ mysqldump -u root -p --all-databases database_dump.txt
password ******–all-databases 选项在 MySQL 3.23.12 及以后版本加入。
该方法可用于实现数据库的备份策略。
3、将数据表及数据库拷贝至其他主机
如果你需要将数据拷贝至其他的 MySQL 服务器上, 你可以在 mysqldump 命令中指定数据库名及数据表。
在源主机上执行以下命令将数据备份至 dump.txt 文件中:
$ mysqldump -u root -p database_name table_name dump.txt
password *****如果完整备份数据库则无需使用特定的表名称。
如果你需要将备份的数据库导入到MySQL服务器中可以使用以下命令使用以下命令你需要确认数据库已经创建
$ mysql -u root -p database_name dump.txt
password *****你也可以使用以下命令将导出的数据直接导入到远程的服务器上但请确保两台服务器是相通的是可以相互访问的
$ mysqldump -u root -p database_name \| mysql -h other-host.com database_name以上命令中使用了管道来将导出的数据导入到指定的远程主机上。
则无需使用特定的表名称。
如果你需要将备份的数据库导入到MySQL服务器中可以使用以下命令使用以下命令你需要确认数据库已经创建
$ mysql -u root -p database_name dump.txt
password *****你也可以使用以下命令将导出的数据直接导入到远程的服务器上但请确保两台服务器是相通的是可以相互访问的
$ mysqldump -u root -p database_name \| mysql -h other-host.com database_name以上命令中使用了管道来将导出的数据导入到指定的远程主机上。
