数据库教程之字符集.安全管理.维护

¶一、字符集和校对顺序

¶1、字符集和校对顺序

在MySQL的正常数据库活动（ SELECT 、 INSERT 等）中，不需要操心太多的东西。使用何种字符集和校对的决定在服务器、数据库和表级进行。

1. 字符集为字母和符号的集合；
2. 编码为某个字符集成员的内部表示；
3. 校对为规定字符如何比较的指令。

¶2、使用字符集和校对顺序

¶2.1 查看支持的字符集

使用命令：SHOW CHARACTER SET;

callation为校对顺序！

这条语句显示所有可用的字符集以及每个字符集的描述和默认校对。

mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset           | binary              |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
.....
.....

¶2.2 查看支持的校对顺序

非常多，展示部分！

用来显示所有可用的校对，以及它们适用的字符集。有的字符集具有不止一种校对。

使用命令：SHOW COLLATION;

mysql> SHOW COLLATION;
+----------------------------+----------+-----+---------+----------+---------+---------------+
| Collation                  | Charset  | Id  | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+----------+-----+---------+----------+---------+---------------+
| armscii8_bin               | armscii8 |  64 |         | Yes      |       1 | PAD SPACE     |
| armscii8_general_ci        | armscii8 |  32 | Yes     | Yes      |       1 | PAD SPACE     |
| ascii_bin                  | ascii    |  65 |         | Yes      |       1 | PAD SPACE     |
| ascii_general_ci           | ascii    |  11 | Yes     | Yes      |       1 | PAD SPACE     |
| big5_bin                   | big5     |  84 |         | Yes      |       1 | PAD SPACE     |
| big5_chinese_ci            | big5     |   1 | Yes     | Yes      |       1 | PAD SPACE     |
| binary                     | binary   |  63 | Yes     | Yes      |       1 | NO PAD        |
| cp1250_bin                 | cp1250   |  66 |         | Yes      |       1 | PAD SPACE     |
| cp1250_croatian_ci         | cp1250   |  44 |         | Yes      |       1 | PAD SPACE     |
| cp1250_czech_cs            | cp1250   |  34 |         | Yes      |       2 | PAD SPACE     |
| cp1250_general_ci          | cp1250   |  26 | Yes     | Yes      |       1 | PAD SPACE     |
.....
.....

¶2.3 确认默认字符集和校对顺序

通常系统管理在安装时定义一个默认的字符集和校对。

此外，也可以在创建数据库时，指定默认的字符集和校对。

字符集很少是服务器范围（甚至数据库范围）的设置。不同的表，甚至不同的列都可能需要不同的字符集，而且两者都可以在创建表时指定。

为了确定所用的字符集和校对，可以使用以下语句：

-- 默认字符集
mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+--------------------------+
| Variable_name            | Value                    |
+--------------------------+--------------------------+
| character_set_client     | utf8mb4                  |
| character_set_connection | utf8mb4                  |
| character_set_database   | utf8mb4                  |
| character_set_filesystem | binary                   |
| character_set_results    | utf8mb4                  |
| character_set_server     | utf8mb4                  |
| character_set_system     | utf8                     |
| character_sets_dir       | D:\MySQL\share\charsets\ |
+--------------------------+--------------------------+
8 rows in set, 1 warning (0.00 sec)

-- 默认校对顺序
mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set, 1 warning (0.00 sec)

¶2.4 指定字符集和校对顺序

在CREATE语句最后加上DEFAULT CHARACTER SET 字符集名 COLLATE 校对顺序名;

如果不指定，则使用2.3中的默认值！

CREATE TABLE mytable
(
    columnn1 INT，
    columnn2 VARCHAR(10)
) DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;

¶2.5 单独对某列设定字符集和校对顺序

既对整个表设定也对某个列设定！

CREATE TABLE mytable
(
columnn1 INT，
columnn2 VARCHAR(10)，
co1umnn3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
) DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;

¶2.6 对ORDER BY子句设定校对顺序

通过对该排序设定校对顺序，可以临时区分大小写来排序！即latin1_genera1_Cs可以区分大小写的校对顺序。

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

¶2.7 其他支持设定校对顺序的语句

COLLATE 还可以用于 GROUP BY 、 HAVING 、聚集函数、别名等。

如果绝对需要，串可以在字符集之间进行转换。为此，使用 Cast() 或 Convert ()函数。

¶二、安全管理

¶1、访问控制

说白了就是分配给谁多大的权限进行数据库操作！

MySQL默认有一个root用户，对MySQL服务具有完全的控制！在日常工作中，决不能使用root，应该创建一系列账号，用于管理及用户使用！

仅在绝对需要时使用它（或许在你不能登录其他管理账号时使用）。不应该在日常的MySQL操作中使用 root 。

举例子如下：

• 多数用户只需要对表进行读和写，但少数用户甚至需要能创建和删除表；
• 某些用户需要读表，但可能不需要更新表；
• 你可能想允许用户添加数据，但不允许他们删除数据；
• 某些用户（管理员）可能需要处理用户账号的权限，但多数用户不需要；
• 你可能想让用户通过存储过程访问数据，但不允许他们直接访问数据；
• 你可能想根据用户登录的地点限制对某些功能的访问。

¶2、管理用户

¶2.1 创建用户账号

GRANT 语句也可以创建用户账号，但一般来说CREATE USER是最清楚和最简单的句子。此外，也可以通过直接插入行到 user 表来增加用户，不过为安全起见，一般不建议这样做。MySQL用来存储用户账号信息的表（以及表模式等）极为重要，对它们的任何毁坏都可能严重地伤害到MySQL服务器。因此，相对于直接处理来说，最好是用标记和函数来处理这表。

语法： CREATE USER 用户名 IDENTIFIED BY '口令'

该口令为纯文本，MySQL会在存入user表之前进行加密！

注意：查询用户可到mysql表下的user表查询！

• user表中localhost表示仅可在本机使用此用户登录数据库
• user表中通配符 % 代表可以在任意主机使用此用户登录数据库
• 创建用户时不添加主机名则默认为%！

CREATE USER ben IDENTIFIED BY 'p@$$wOrd' ;

CREATE USER 'xxx'@'localhost' IDENTIFIED BY '123';
CREATE USER 'xxx'@'%' IDENTIFIED BY '123';

CREATE USER 创建一个新用户账号。在创建用户账号时不一定需要口令，不过这个例子用 IDENTIFIED BY 'p@$$wOrd' 给出了一个口令。

指定散列口令：

语法：CREATE USER 用户名 IDENTIFIED BY PASSWORD

重命名用户名：

语法：RENAME USER 用户名 TO 新用户名

RENAME USER ben TO bforta;

¶2.2 删除用户账号

语法： DROP USER 用户名

DROP USER 删除用户账号和所有相关的账号权限。

在MySQL 5以前， DROP USER 只能用来删除用户账号，不能删除相关的权限。因此，如果使用旧版本的MySQL，需要先用 REVOKE 删除与账号相关的权限，然后再用 DROP USER 删除账号。

DROP USER ben;

¶2.3 设置访问权限

新建的用户没有权限，只能进行登录操作！

¶2.3.1 查看用户权限

语法：SHOW GRANTS 用户名

下面显示有一个权限： USAGE ON *.*，USAGE表名没有任何权限。

MySQL的权限用用户名和主机名结合定义。如果不指定主机名，则使用默认的主机名 % （授予用户访问权限而不管主机名）。如：'ben' @ '%'

mysql> SHOW GRANTS FOR ben;
+---------------------------------+
| Grants for ben@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO `ben`@`%` |
+---------------------------------+
1 row in set (0.00 sec)

¶2.3.2 授予用户权限

三步：

• 要授予的权限；
• 被授予访问权限的数据库或表；
• 用户名。

此 GRANT 允许用户在crashcourse.*（ crashcourse 数据库的所有表）上使用 SELECT 。通过只授予SELECT 访问权限，用户 ben对 crashcourse 数据库中的所有数据具有只读访问权限。

语法：GRANT 权限 ON 目标数据库 TO 用户

多个权限使用逗号分隔即可！

GRANT SELECT ON crashcourse.* TO ben;

-- 使用SHOW FRANTS 查询权限
mysql> SHOW GRANTS FOR ben;
+----------------------------------------------+
| Grants for ben@%                             |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `ben`@`%`              |
| GRANT SELECT ON `crashcourse`.* TO `ben`@`%` |
+----------------------------------------------+
2 rows in set (0.00 sec)

¶2.3.3 撤销用户权限

语法：REVOKE 权限 ON 目标数据库 FROM 用户

撤销的权限必须存在，否则会报错！

多个权限使用逗号分隔即可！

REVOKE SELECT ON crashcourse.* FROM bforta;

GRANT 和 REVOKE 的几个层次：

• 整个服务器，使用 GRANT ALL 和 REVOKE ALL；
• 整个数据库，使用 ON database.*；
• 特定的表，使用 ON database.table；
• 特定的列；
• 特定的存储过程。

¶2.3.4 权限信息如下

¶2.4 更改口令（密码）

语法：SET PASSWORD FOR 用户名 = Passward('新口令');

或 update user set password = password(‘新密码’) where user = ‘用户名’;

SET PASSWORD FOR ben = Password('sdjfaosljdfk');

# 或
update user set password = password(‘xxx’) where user = ‘ben’;

设置自己口令：

在不指定用户名时， SET PASSWORD 更新当前登录用户的口令。

SET PASSWORD = Password('sdjfaosljdfk');

¶三、数据库维护

¶1、备份数据

像所有数据一样，MySQL的数据也必须经常备份。由于MySQL数据库是基于磁盘的文件，普通的备份系统和例程就能备份MySQL的数据。但是，由于这些文件总是处于打开和使用状态，普通的文件副本备份不一定总是有效。

首先刷新未写数据 为了保证所有数据被写到磁盘（包括索引数据），可能需要在进行备份前使用 FLUSH TABLES 语句。

解决方案：

1. 使用命令行实用程序 mysqldump 转储所有数据库内容到某个外部文件。在进行常规备份前这个实用程序应该正常运行，以便能正确地备份转储文件。
2. 可用命令行实用程序 mysqlhotcopy 从一个数据库复制所有数据（并非所有数据库引擎都支持这个实用程序）。
3. 可以使用MySQL的 BACKUP TABLE 或 SELECT INTO OUTFILE 转储所有数据到某个外部文件。这两条语句都接受将要创建的系统文件名，此系统文件必须不存在，否则会出错。数据可以用 RESTORETABLE 来复原。

代码示例：

# 备份
mysqldump -u用户名 -p密码 需要备份的数据库名称 > 保存路径

# 还原：
a.登录数据库
b.创建数据库
c.使用数据库
d.执行语法：source 文件路径

¶2、进行数据库维护

¶2.1 检查表键是否正确

语法： ANALYZE TABLE 表名

mysql> ANALYZE TABLE orders;
+-------------+---------+----------+----------+
| Table       | Op      | Msg_type | Msg_text |
+-------------+---------+----------+----------+
| test.orders | analyze | status   | OK       |
+-------------+---------+----------+----------+
1 row in set (0.13 sec)

¶2.2 对表进行多种问题检查

语法： CHECK TABLE 表名

在 MyISAM 表上还对索引进行检查。 CHECK TABLE 支持一系列的用于 MyISAM 表的方式。CHANGED 检查自最后一次检查以来改动过的表。 EXTENDED 执行最彻底的检查， FAST 只检查未正常关闭的表， MEDIUM 检查所有被删除的链接并进行键检验， QUICK 只进行快速扫描。如下所示， CHECK TABLE 发现和修复问题：

• 如果 MyISAM 表访问产生不正确和不一致的结果，可能需要用REPAIR TABLE 来修复相应的表。这条语句不应该经常使用，如果需要经常使用，可能会有更大的问题要解决。
• 如果从一个表中删除大量数据，应该使用 OPTIMIZE TABLE 来收回所用的空间，从而优化表的性能。

mysql> CHECK TABLE orders，orderitems ;
+--------------------------+-------+----------+------------------------------------------------+
| Table                    | Op    | Msg_type | Msg_text
               |
+--------------------------+-------+----------+------------------------------------------------+
| test.orders，orderitems  | check | Error    | Table 'test.orders，orderitems' doesn't exist  |
| test.orders，orderitems  | check | status   | Operation failed
               |
+--------------------------+-------+----------+------------------------------------------------+
2 rows in set (0.00 sec)

¶3、诊断启动问题

服务器启动问题通常在对MySQL配置或服务器本身进行更改时出现。MySQL在这个问题发生时报告错误，但由于多数MySQL服务器是作为系统进程或服务自动启动的，这些消息可能看不到。在排除系统启动问题时，首先应该尽量用手动启动服务器。MySQL服务器自身通过在命令行上执行 mysqld 启动。

几个重要的 mysqld命令行选项：

• --help 显示帮助——一个选项列表；
• --safe-mode 装载减去某些最佳配置的服务器；
• --verbose 显示全文本消息（为获得更详细的帮助消息与 --help联合使用）；
• --version 显示版本信息然后退出。

¶4、查看日志文件

MySQL维护管理员依赖的一系列日志文件。主要的日志文件有以下几种。

• 错误日志。它包含启动和关闭问题以及任意关键错误的细节。此日志通常名为 hostname.err ，位于 data 目录中。此日志名可用--log-error 命令行选项更改。
• 查询日志。它记录所有MySQL活动，在诊断问题时非常有用。此日志文件可能会很快地变得非常大，因此不应该长期使用它。此日志通常名为 hostname.log ，位于 data 目录中。此名字可以用--log 命令行选项更改。
• 二进制日志。它记录更新过数据（或者可能更新过数据）的所有语句。此日志通常名为 hostname-bin ，位于 data 目录内。此名字可以用 --log-bin 命令行选项更改。注意，这个日志文件是MySQL5中添加的，以前的MySQL版本中使用的是更新日志。
• 缓慢查询日志。顾名思义，此日志记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。此日志通常名为hostname-slow.log ， 位 于 data 目 录 中 。 此 名 字 可 以 用--log-slow-queries 命令行选项更改。

在使用日志时，可用 FLUSH LOGS 语句来刷新和重新开始所有日志文件。

¶四、改善性能

数据库管理员把他们生命中的相当一部份时间花在了调整、试验以改善DBMS性能之上。在诊断应用的滞缓现象和性能问题时，性能不良的数据库（以及数据库查询）通常是最常见的祸因。

下面的内容并不能完全决定MySQL的性能。只是提供进行性能优化探讨和分析的一个出发点。

• 首先，MySQL（与所有DBMS一样）具有特定的硬件建议。在学习和研究MySQL时，使用任何旧的计算机作为服务器都可以。但对用于生产的服务器来说，应该坚持遵循这些硬件建议。
• 一般来说，关键的生产DBMS应该运行在自己的专用服务器上。
• MySQL是用一系列的默认设置预先配置的，从这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等。（为查看当前设置，可使用 SHOW VARIABLES; 和 SHOW STATUS; 。）
• MySQL一个多用户多线程的DBMS，换言之，它经常同时执行多个任务。如果这些任务中的某一个执行缓慢，则所有请求都会执行缓慢。如果你遇到显著的性能不良，可使用 SHOW PROCESSLIST显示所有活动进程（以及它们的线程ID和执行时间）。你还可以用KILL 命令终结某个特定的进程（使用这个命令需要作为管理员登录）。
• 总是有不止一种方法编写同一条 SELECT 语句。应该试验联结、并、子查询等，找出最佳的方法。
• 使用 EXPLAIN 语句让MySQL解释它将如何执行一条 SELECT 语句。
• 一般来说，存储过程执行得比一条一条地执行其中的各条MySQL语句快。
• 应该总是使用正确的数据类型。
• 决不要检索比需求还要多的数据。换言之，不要用 SELECT * （除非你真正需要每个列）。
• 有的操作（包括 INSERT ）支持一个可选的 DELAYED 关键字，如果使用它，将把控制立即返回给调用程序，并且一旦有可能就实际执行该操作。
• 在导入数据时，应该关闭自动提交。你可能还想删除索引（包括FULLTEXT 索引），然后在导入完成后再重建它们。
• 必须索引数据库表以改善数据检索的性能。确定索引什么不是一件微不足道的任务，需要分析使用的 SELECT 语句以找出重复的WHERE 和 ORDER BY 子句。如果一个简单的 WHERE 子句返回结果所花的时间太长，则可以断定其中使用的列（或几个列）就是需要索引的对象。
• 你的 SELECT 语句中有一系列复杂的 OR 条件吗？通过使用多条SELECT 语句和连接它们的 UNION 语句，你能看到极大的性能改进。
• 索引改善数据检索的性能，但损害数据插入、删除和更新的性能。如果你有一些表，它们收集数据且不经常被搜索，则在有必要之前不要索引它们。（索引可根据需要添加和删除。）
• LIKE 很慢。一般来说，最好是使用 FULLTEXT 而不是 LIKE 。
• 数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改，理想的优化和配置也会改变。
• 最重要的规则就是，每条规则在某些条件下都会被打破。