九、mysql学习笔记——mysql事务机制及数据导入导出（六）

mysql学习笔记（六）

上一节学习了mysql的null值处理、正则化、序列（有序数列）等mysql格式化问题，本节将学习mysql的事务处理机制、临时表、数据导入、导出，查看mysql版本信息，mysql的数据安全问题等。

 

1、mysql事务

在数据管理系统中，封装一些固定的mysql语句，来实现对大批量数据的统一操作，如企业里由于某人离职需要删除该人的所有资料，包括：个人基本信息、工资、以往业绩等。这种统一的操作就称为“事务”。mysql事务的特点：

A、（atomicity）原子性：执行事务不会中途停止，要么不执行，要么执行所有语句；当执行过程中发生错误时，会回滚到事务执行之前，仿佛没有执行过事务。

C、（consistency）一致性：在事务开始前和结束后，只是记录发生了变化，但数据结构和完整性都仍然保持一致，“事务”可以保证数据的完整性，不会丢失。

L、（isolation）隔离性：允许多个并发事务同时执行，对数据库进行操作，而互相之间不受影响且不破坏数据。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

D、（durability）持久性：在mysql中只有使用了InnoDB数据库引擎的数据库才能使用“事务”操作；事务的操作是永久的，不会改变。

1）MYSQL 事务处理主要有两种方法：

（1）用 BEGIN, ROLLBACK, COMMIT来实现

• BEGIN 开始一个事务
• ROLLBACK 事务回滚，数据库状态回到事务执行之前
• COMMIT 事务提交

（2）直接用 SET 来改变 MySQL 的自动提交模式:

正常情况下，事务的执行结果是自动提交的，不过也可以使用set语句手动控制提交方式。

• SET AUTOCOMMIT=0 禁止自动提交
• SET AUTOCOMMIT=1 开启自动提交

2）事务控制语句：

• BEGIN 或 START TRANSACTION 显式地开启一个事务；
• COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
• ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
• SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
• RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
• ROLLBACK TO identifier 把事务回滚到标记点；
• SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

 

2、临时表

临时表用来放中间临时数据，在mysql关闭连接后会自动销毁，这时再用show tables,则会显示该临时表不存在。

创建临时表：create temporary table table_name(...);  其他操作均与普通数据表类似。

 

3、打印mysql元数据

以下命令语句可以在 MySQL 的命令提示符使用，也可以在脚本中 使用，如PHP脚本。

命令	描述
SELECT VERSION( )	服务器版本信息
SELECT DATABASE( )	当前数据库名 (或者返回空)
SELECT USER( )	当前用户名
SHOW STATUS	服务器状态
SHOW VARIABLES	服务器配置变量

 

4、mysql注入问题——安全

mysql注入问题是指用户通过向服务器发送请求时恶意插入一些mysql命令，从而对网站的数据库执行恶意操作损害企业利益。因此，需要对web表单或用户输入的数据进行格式过滤：通常限制用户输入为固定的格式，如输入的用户名必须为字母、数字及下划线的组合，且用户名长度为 8 到 20 个字符之间。

防止SQL注入，我们需要注意以下几个要点：

1. 永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和 双"-"进行转换等。

2. 永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。

3. 永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。

4. 不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。

5. 应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装

6. sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。

 

 

5、mysql导出数据

 

（1）SELECT ... INTO OUTFILE 语句

使用select... into outfile'...' 来导出mysql数据，如：

• 直接导出表中所有数据，存入为txt文件：

select *from table_name into outfile'...\test.txt';     

• 设置保存格式：每个数据之间用‘，’分隔、单个数据用‘ ” ‘包围、行之间换行，实际导出格式为.csv：

select *from table_name into outfile'...\test.txt' fields terminated by ',' enclosed by ' " ' lines terminated by '\r\n';   

• 还可以指定保存字段，各值用逗号隔开。这种格式可以被许多程序使用：

select column1,column2,column1+column2 from table_name into outfile'...\test.txt' fields terminated by ','  optionally enclosed by ' " ' lines terminated by '\n';  

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 是 mysql 用于转存储数据库的实用程序。它主要产生一个 SQL 脚本，其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。

使用 mysqldump 导出数据需要使用 --tab 选项来指定导出文件指定的目录，该目标必须是可写的。

以下实例将数据表 nowcoder_tbl 导出到 /tmp 目录中：

1 2 3

$ mysqldump -u root -p --no-create-info \                 --tab=/tmp NOWCODER nowcoder_tbl password ******

 

（3）导出 SQL 格式的数据

• 导出 SQL 格式的数据到指定文件，如下所示：

1 2	$ mysqldump -u root -p NOWCODER nowcoder_tbl > dump.txt password ******

• 如果你需要导出整个数据库的数据，可以使用以下命令：

1 2	$ mysqldump -u root -p NOWCODER > database_dump.txt password ******

• 如果需要备份所有数据库，可以使用以下命令：

1 2	$ mysqldump -u root -p --all-databases > database_dump.txt password ******

该方法可用于实现数据库的备份策略。

如果你需要将数据拷贝至其他的 MySQL 服务器上, 你可以在 mysqldump 命令中指定数据库名及数据表。

• 在源主机上执行以下命令，将数据备份至 dump.txt 文件中:

1 2	$ mysqldump -u root -p database_name table_name > dump.txt password *****

如果完整备份数据库，则无需使用特定的表名称。

• 如果你需要将备份的数据库导入到MySQL服务器中，可以使用以下命令，使用以下命令你需要确认数据库已经创建：

1 2	$ mysql -u root -p database_name < dump.txt password *****

• 你也可以使用以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的，是可以相互访问的：

1 2	$ mysqldump -u root -p database_name \        | mysql -h other-host.com database_name

以上命令中使用了管道来将导出的数据导入到指定的远程主机上。

 

6、mysql导入数据

一共有四种方法。

（1）mysql命令导入

使用 mysql 命令导入语法格式为：

1	mysql -u用户名    -p密码    <  要导入的数据库数据

实例：

1	# mysql -uroot -p123456 < nowcoder.sql

以上命令将将备份的整个数据库 nowcoder.sql 导入。

（2）source导入

source 命令导入数据库需要先登录到数库终端：

1 2 3 4

mysql> create database abc;      # 创建数据库 mysql> use abc;                  # 使用已创建的数据库 mysql> set names utf8;           # 设置编码 mysql> source /home/abc/abc.sql  # 导入备份数据库

（3）使用load data导入

与前面的mysql导出数据select...into outfile’xx.txt' 相似，导入mysql数据需要用到load data infile’xx.txt' into table_name;如：

1	mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

如果指定LOCAL关键词，则表明从客户主机上按路径读取文件。如果没有指定，则文件在服务器上按路径读取文件。两个命令的 FIELDS 和 LINES 子句的语法是一样的。两个子句都是可选的，但是如果两个同时被指定，FIELDS 子句必须出现在 LINES 子句之前。

1 2 3

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，则数据导入语法如下：

1 2	mysql> LOAD DATA LOCAL INFILE 'dump.txt'     -> INTO TABLE mytbl (b, c, a);

（4）使用mysqlimprot导入

mysqlimport 客户端提供了 LOAD DATA INFILEQL 语句的一个命令行接口。mysqlimport 的大多数选项直接对应 LOAD DATA INFILE 子句。

从文件 dump.txt 中将数据导入到 mytbl 数据表中, 可以使用以下命令：

1 2	$ mysqlimport -u root -p --local mytbl dump.txt password *****

mysqlimport 命令可以指定选项来设置指定格式,命令语句格式如下：

1 2 3

$ mysqlimport -u root -p --local --fields-terminated-by=":" \    --lines-terminated-by="\r\n"  mytbl dump.txt password *****

mysqlimport 语句中使用 --columns 选项来设置列的顺序：

1 2 3

$ mysqlimport -u root -p --local --columns=b,c,a \     mytbl dump.txt password *****

mysqlimport的常用选项介绍：

选项	功能
-d or --delete	新数据导入数据表中之前删除数据数据表中的所有信息
-f or --force	不管是否遇到错误，mysqlimport将强制继续插入数据
-i or --ignore	mysqlimport跳过或者忽略那些有相同唯一 关键字的行， 导入文件中的数据将被忽略。
-l or -lock-tables	数据被插入之前锁住表，这样就防止了， 你在更新数据库时，用户的查询和更新受到影响。
-r or -replace	这个选项与－i选项的作用相反；此选项将替代 表中有相同唯一关键字的记录。
--fields-enclosed- by= char	指定文本文件中数据的记录时以什么括起的， 很多情况下 数据以双引号括起。 默认的情况下数据是没有被字符括起的。
--fields-terminated- by=char	指定各个数据的值之间的分隔符，在句号分隔的文件中， 分隔符是句号。您可以用此选项指定数据之间的分隔符。 默认的分隔符是跳格符（Tab）
--lines-terminated- by=str	此选项指定文本文件中行与行之间数据的分隔字符串 或者字符。 默认的情况下mysqlimport以newline为行分隔符。 您可以选择用一个字符串来替代一个单个的字符： 一个新行或者一个回车。

mysqlimport 命令常用的选项还有 -v 显示版本（version）， -p 提示输入密码（password）等。