09 事务 字符集和校对 安全管理 数据库维护

9.1 事务

事务处理(transaction processing)可以用来维护数据库的完整性。它保证成批的MySQL语句要么完全执行,要么完全不执行。下面是关于事务的几个术语:

  • 回退(rollback):撤销指定的SQL语句

  • 提交(commit):将未存入的SQL语句结果写入数据库表

  • 保留点(savepoint):指事务处理中设置的临时占位符,你可以对它发布回退(不同于回退整个事务)

使用ROLLBACK或COMMIT后,事务会关闭。

使用ROLLBACK

我们delete之后,select为空,rollback之后再select,数据又恢复了。

注意只有INSERT、UPDATE和DELETE能被ROLLBACK

mysql> SELECT * FROM ordertotals;
+-----------+---------+
| order_num | total |
+-----------+---------+
| 20005 | 158.86 |
| 20009 | 40.78 |
| 20006 | 58.30 |
| 20007 | 1060.00 |
| 20008 | 132.50 |
| 20008 | 132.50 |
+-----------+---------+
6 rows in set (0.01 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.01 sec)
mysql> DELETE FROM ordertotals;
Query OK, 6 rows affected (0.02 sec)
mysql> SELECT * FROM ordertotals;
Empty set (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM ordertotals;
+-----------+---------+
| order_num | total |
+-----------+---------+
| 20005 | 158.86 |
| 20009 | 40.78 |
| 20006 | 58.30 |
| 20007 | 1060.00 |
| 20008 | 132.50 |
| 20008 | 132.50 |
+-----------+---------+

使用COMMIT

一般的SQL语句是自动提交的,事务中的语句不会自动提交。

START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

使用保留点

ROLLBACK直接使用会撤销整个事务,如果想撤销部分事务,用保留点便可以实现

SAVEPOINT delete1;
...
some sql statements...
...
ROLLBACK TO delete1;

更改默认的提交行为

前面说过事务的外的操作默认会自动提交,如果不想自动提交,可以设置autocommit的值来实现。autocommit这个标志是正对每个连接,而不是服务器使用的。

SET autocommit=0;

9.2 字符集和校对

  • 字符集:字母和符号的集合

  • 编码:某个字符集成员的内部表示

  • 校对:规定字符如何比较,在排序中很重要

下面两条语句可以分别查看所支持的字符集合校对。一个字符集可以对应多种校对,但一般有一个默认校对。

SHOW CHARACTER SET;
SHOW COLLATION;

MySQL确定字符集和校对的规则:

  • 指定了字符集和校对,则使用指定的

  • 只制定了字符集,则使用此字符集及其默认校对

  • 都没指定,则使用数据库的默认字符集合校对

创建表时指定字符集和校对

CREATE TABLE mytable
(
column1 INT,
column2 VARCHAR(10)
) DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;

对列指定单独和字符集和校对

CREATE TABLE mytable
(
column1 INT,
column2 VARCHAR(10),
column3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
) DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;

还以以在select中临时指定校对

SELECT * FROM customers
ORDER BY lastname, firstname COLLATE latin1_general_cs;

9.3 安全管理

MySQL用户账号和信息存储在名为mysql的数据库中。但一般不建议直接操作mysql数据库。

创建用户

CREATE USER ben IDENFITED BY 'password'

重名命

RENAME USER ben TO beforta;

删除用户

DROP USER beforta;

设置访问权限

mysql> SHOW GRANTS FOR beforta;
+-------------------------------------+
| Grants for beforta@% |
+-------------------------------------+
| GRANT USAGE ON *.* TO 'beforta'@'%' |
+-------------------------------------+

可以看到beforta对*.*有USAGE权限,*.*其实表示什么都没有。另外这里用户定义为user@host的形式,host不指定的话默认为%。下面为beforta赋予在mnysql_crash_course进行select的权限。

mysql> GRANT SELECT ON mysql_crash_course.* TO beforta;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW GRANTS FOR beforta;
+----------------------------------------------------------+
| Grants for beforta@% |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'beforta'@'%' |
| GRANT SELECT ON `mysql_crash_course`.* TO 'beforta'@'%' |
+----------------------------------------------------------+

revoke可以撤销权限

REVOKE SELECT ON myqsl_crash_course.* FROM beforta;

GRANT和REVOKE可以在几个层次上控制访问权限:

  • 整个服务器,使用GRANT ALL;REVOKE ALL;

  • 整个数据库,使用ON database_name.*;

  • 特定的表,使用ON database_name.table_name;

  • 特定的列

  • 特定的存储过程

更改密码

创建用户时我们用的是纯文本,更改密码时必须用Password函数对纯文本进行加密。

SET PASSWORD FOR beforta = Password('new_password');

直接设置当前用户的密码

SET PASSWORD = Password('new_passord');

9.4 数据库维护

备份数据

几种方案:

  • 命令行程序mysqldump

  • 命令行程序mysqlhotcopy

  • MySQL中的BACKUP TABLESELECT INTO OUTFILE

数据库的维护

  • ANALYZE TABLE 检查表键是否正确

  • CHECK TABLE 对表进行检查

查看日志文件

主要的日志有以下几种

  • 错误日志:通常名为hostname.err,位于data目录中

  • 查询日志:记录所有MySQL活动,通常名为hostname.log,位于data目录中

  • 二进制日志:记录更新过数据的所有语句。通常名为hostname-bin,位于data目录中

  • 缓慢查询日志:记录执行缓慢的查询。对数据库优化很有用。通常名为hostname-slow.log,位于data目录中