PHP MySQL 入门教程（八）

原文：Beginning PHP and MySQL

协议：CC BY-NC-SA 4.0

二十四、众多 MySQL 客户端

MySQL 附带了相当多的实用程序，或称*客户机，*其中的每一个都提供了执行与数据库服务器管理相关的各种任务的接口。本章概述了最常用的客户端，并深入介绍了原生 mysql 和 mysqladmin 客户端。 ¹ 因为 MySQL 手册已经出色地提供了每个客户端的概述，所以这一章将重点放在那些您在日常管理活动中最有可能经常使用的特性上。

本章首先介绍捆绑的客户端。不需要安装额外的工具，但是当然，不是所有的用户都习惯使用命令行；因此，MySQL 开发人员和第三方多年来创造了许多强大的基于 GUI 的管理解决方案，其中一些我将在本章后面介绍。

命令行客户端简介

MySQL 与相当多的客户端程序捆绑在一起，其中许多程序即使使用，也很少使用。但是，当连接到无法远程访问的远程主机上的数据库时，有两种方法特别有用。这一部分提供了对这两个客户端(mysql和mysqladmin)的详细介绍，并在最后简要介绍了其他几个客户端。

mysql 客户端

客户端是一个有用的 SQL shell，能够管理 MySQL 服务器的几乎所有方面，包括创建、修改和删除表和数据库；创建和管理用户；查看和修改服务器配置；和查询表数据。虽然大多数时候您可能会通过基于 GUI 的应用或 API 来使用 MySQL，但是这个客户机对于执行各种管理任务来说是非常宝贵的，特别是考虑到它在 shell 环境中的可脚本化功能。其一般用法语法如下:

mysql [options] [database_name] [noninteractive_arguments]

客户端可以在交互或非交互模式下使用，这两种模式都将在本节中介绍。无论您使用哪一种，通常都需要提供连接选项。具体所需的凭据取决于您的服务器配置；然而，你通常需要一个主机名(--host=, -h、用户名(--user=, -u和密码(--password=, -p。密码选项可以在有密码或没有密码的情况下使用。如果您在命令行中包含密码，旁观者就有可能看到它。如果您忽略了密码。客户端会提示输入密码，但输入时不会显示实际的密码。通常，您会希望包含目标数据库名称(--database=, -D，以节省进入客户机后执行use命令的额外步骤。尽管顺序无关紧要，但连接选项通常是这样输入的:

$ mysql -h hostname -u username -p -D databasename

请注意，命令行中不包含密码，尽管它可能如上所述。例如，以下是使用用户名Jason和数据库employees连接到位于 www.example.com 的 MySQL 服务器的尝试:

$ mysql -h www.example.com -u jason -p -D employees

与其他连接选项不同，数据库选项实际上是可选的，前提是您将数据库名称放在行尾。因此，您可以通过省略它来节省一些击键次数，如下所示:

$ mysql -h www.example.com -u jason -p employees

最后，最常见的情况是连接到数据库所在的本地开发环境。在这种情况下，您可以完全放弃引用主机，因为 MySQL 会默认假设您希望连接到 localhost:

$ mysql –u jason –p employees

您还可以包括其他选项，其中许多将在后面的“有用的 mysql 选项”一节中介绍，或者执行命令来提示输入密码。如果您的凭证有效，您将被授予访问客户端界面的权限，或者被允许执行命令行中包含的任何非交互式参数。虽然可以提供密码作为一个选项，但是您不应该这样做，因为密码将被记录在您的命令历史中！然而，如果 MySQL 客户端是从脚本中调用的，那么这是一个有效的用法。这需要通过设置足够的权限来保护帐户和脚本。

与 MySQL 交互

要在交互模式下使用 MySQL，需要先进入界面。如前所述，您可以通过传递适当的凭证来实现这一点。在前一个例子的基础上，假设您想要与驻留在您的开发环境中的 dev_c orporate_com数据库进行交互:

$ mysql -u jason -p employees

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 387
Server version: 5.5.9-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

为了说明 MySQL 和 MariaDB 之间的细微差别，如果您安装了 MariaDB，下面是相同命令的输出:

Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [employees]>

通过mysql客户端连接后，您就可以开始执行 SQL 命令了。例如，要查看所有现有数据库的列表，请使用以下命令:

mysql> show databases;
+------------------------------+
| Database                     |
+------------------------------+
| information_schema           |
| employees                    |
| mysql                        |
| test                         |
+------------------------------+
3 rows in set (0.00 sec)

如果您在没有明确识别数据库的情况下进入服务器，并且想要开始使用特定的数据库，请使用use命令:

MariaDB  [(none)]> use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [employees]>

一旦切换到mysql数据库上下文，就可以用这个命令查看所有表:

mysql> show tables;

这将返回以下内容:

+-----------------------------+
| Tables_in_employees         |
+-----------------------------+
| departments                 |
| dept_emp                    |
| dept_manager                |
| employees                   |
| salaries                    |
| titles                      |
+-----------------------------+
6 rows in set (0.00 sec)

要查看其中一个表的结构，例如,host表，使用以下命令:

mysql> describe employees;

这将返回以下内容:

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int(11)       | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

您还可以执行 SQL 查询，如insert、select、update和delete。例如，假设您想要选择位于employees表中的emp_no、first_name和last_name值，按last_name对结果进行排序，并将结果限制为前三个:

mysql> select emp_no, first_name, last_name from employees order by last_name limit 3;

总之，您可以通过 MySQL 能够理解的mysql客户端执行任何查询。

您可以通过执行以下任意命令来退出 mysql 客户端:quit、exit、\q或Ctrl-D。

以批处理模式使用 mysql

mysql客户机还提供批处理模式功能，用于将模式和数据导入数据库，并将输出通过管道传输到另一个目的地。例如，您可以通过让mysql客户端使用<操作符消费/path/to/file的内容来执行驻留在文本文件中的 SQL 命令，如下所示:

%>mysql [options] < /path/to/file

这个功能有很多用途。例如，该功能的一个可能用途是每天早上通过电子邮件将服务器统计数据发送给系统管理员。例如，假设您想要监控执行时间超过由变量long_query_time定义的时间的查询的数量:

mysql> show variables like "long_query_time";
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)

首先创建一个名为mysql_monitor的没有密码的用户(不应该创建没有密码的帐户，因为这将允许任何人访问该帐户)，只授予该用户对mysql数据库的usage权限:

mysql> grant usage on mysql.* to 'mysql_monitor'@'localhost';

然后，创建一个名为mysqlmon.sql的文件，并在其中添加以下代码行:

show status like "slow_queries";

现在，您可以轻松访问这些数据，而无需首先登录 MySQL 服务器:

$ mysql –u mysql_monitor < mysqlmon.sql
Variable_name    Value
Slow_queries     42

当然，如果您运行的是 OS X 或 Linux，您甚至可以将这个命令打包到它自己的 shell 脚本中，从而节省更多的击键时间:

#!/bin/sh
mysql -u testuser2 < mysqlmon.sql

使用一个容易识别的名称保存该文件，例如mysql_monitor.sh，相应地设置其执行权限，并按如下方式执行:

$ ./monitor.sh
Variable_name    Value
Slow_queries     42

顺便提一下，您也可以在已经登录到mysql客户端的情况下，通过使用source命令来执行文件:

mysql> source mysqlmon.sql
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.00 sec)

有用的 mysql 技巧

本节列举了几个有用的技巧，所有 MySQL 用户在开始使用mysql客户端时都应该知道。

垂直显示结果

使用\G选项以垂直输出格式显示查询结果。这使得返回的数据更加易读。考虑这个例子，其中通过使用\G选项从mysql数据库的db表中选择所有行:

mysql>use mysql;
mysql>select * from db\G
*************************** 1\. row ***************************
   Host: %
   Db: test%
   User:
   Select_priv: Y
   Insert_priv: Y
   Update_priv: Y
   …
*************************** 2\. row ***************************
...

记录查询

当与mysql客户端交互工作时，将所有结果记录到一个文本文件中会很有用，这样您可以在以后查看它们。您可以使用tee或\T选项启动日志记录，后跟一个文件名，如果需要，还可以加上一个路径。例如，假设您想要将会话记录到名为session.sql的文件中:

mysql>\T session.sql
Logging to file 'session.sql'
mysql>show databases;
+-------------+
| Database    |
+-------------+
| mysql       |
| test        |
+-------------+

一旦开始记录，您在这里看到的输出将被记录到session.sql中。要在会话期间的任何时候禁用日志记录，请执行notee或\t。

获取服务器统计信息

执行status或\s命令将检索许多关于当前服务器状态的有用统计数据，包括正常运行时间、版本、TCP 端口、连接类型、执行的查询总数、平均每秒查询数等等。

预防事故

假设您管理一个包含 10，000 名新闻稿成员的表。有一天，你决定使用 mysql 客户端删除一个旧的测试帐户。这是漫长的一天，你不假思索地执行

mysql>DELETE FROM subscribers;

而不是

mysql>DELETE FROM subscribers WHERE email="test@example.com";

哎呀，你刚刚删除了你的整个用户群！希望最近的备份是方便的。作为mysql命令的一个参数，--safe-updates选项通过拒绝执行任何没有附带WHERE子句的DELETE或UPDATE查询来防止这种无意的错误。有趣的是，你也可以用--i-am-a-dummy开关来达到同样的目的！

修改 mysql 提示符

当同时处理驻留在不同服务器上的几个数据库时，您可能很快会弄不清当前使用的是哪一个服务器。为了使位置更明显，修改缺省提示以包括主机名。你可以用几种方法做到这一点。

一种方法是在登录mysql时修改命令行上的提示，像这样:

%>mysql -u jason --prompt="(\u@\h) [\d]> " -p employees

登录到控制台后，会出现如下提示:

(jason@localhost) [employees]>

要使更改永久化，您也可以在[mysql]部分下的my.cnf文件中进行更改:

[mysql]
...
prompt=(\u@\h) [\d]>

最后，在 Linux/Unix 上，您可以通过MYSQL_PS1环境变量在提示符下包含主机名:

%>export MYSQL_PS1="(\u@\h) [\d]> "

注意

MySQL 手册中提供了提示符可用标志的完整列表。

查看配置变量和系统状态

您可以通过SHOW VARIABLES命令查看所有服务器配置变量的完整列表:

mysql>show variables;

这将返回所有可用的系统变量。可用数量取决于 MySQL/MariaDB 的配置和版本。如果您只想查看一个特定的变量，比如默认的表格类型，您可以结合使用这个命令和like:

mysql> show variables like "version";

这将返回以下内容:

+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| version       | 5.5.9-log |
+---------------+-----------+

查看系统状态信息同样简单:

mysql> show status;

这将返回以下内容:

+------------------------------------------+-------------+
| Variable_name                            | Value       |
+------------------------------------------+-------------+
| Aborted_clients                          | 50          |
| Aborted_connects                         | 2           |
...
| Threads_connected                        | 7           |
| Threads_created                          | 399         |
| Threads_running                          | 1           |
| Uptime                                   | 1996110     |
| Uptime_since_flush_status                | 1996110     |
+------------------------------------------+-------------+
287 rows in set (0.00 sec)

要查看状态报告中的单个项目，例如发送到所有客户端的总字节数，请使用以下命令:

mysql> show status like "bytes_sent";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Bytes_sent    | 18393 |
+---------------+-------+

如果您想要检索名称相似的变量组(这通常意味着相似的目的)，您可以使用%通配符。例如，以下命令检索用于跟踪与 MySQL 的查询缓存特性相关的统计数据的所有变量:

mysql>show status like "Qc%";
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 161    |
| Qcache_free_memory      | 308240 |
| Qcache_hits             | 696023 |
| Qcache_inserts          | 449839 |
| Qcache_lowmem_prunes    | 47665  |
| Qcache_not_cached       | 2537   |
| Qcache_queries_in_cache | 13854  |
| Qcache_total_blocks     | 27922  |
+-------------------------+--------+
8 rows in set (0.00 sec)

有用的 mysql 选项

像本章介绍的所有客户端一样，mysql client提供了许多有用的选项，通过命令行传递。这里介绍了许多最重要的选项:

• -- auto-rehash:默认情况下，mysql会创建数据库、表和列名的散列，以便于自动完成(可以用Tab键自动完成数据库、表和列名)。您可以使用--no-auto-rehash禁用此行为。如果您想重新启用它，请使用此选项。如果您不打算使用自动完成，请考虑禁用此选项，这将稍微加快启动时间。

• -- column-names:默认情况下，mysql在每个结果集的顶部包含列名。你可以用--no-column-names禁用它们。如果您想重新启用此行为，请重新使用此选项。

• -- compress，-C:客户端与服务器通信时，启用数据压缩。

• -- database=name , -D:决定使用哪个数据库。当以交互方式使用 MySQL 时，您还可以根据需要使用USE命令在数据库之间切换。

• --default-character-set=character_set:设置字符集。

• -- disable-tee:如果您已经使用选项--tee或命令tee启用了所有查询和结果的日志记录，那么您可以使用此选项禁用此行为。

• --execute=query、-e、query:无需实际进入客户端界面即可执行查询。您可以使用此选项执行多个查询，用分号分隔每个查询。确保用引号将查询括起来，这样 shell 就不会将其误解为多个参数。例如，

• -- force , -f:在非交互模式下使用时，MySQL 可以读取和执行文本文件中的查询。默认情况下，如果出现错误，这些查询的执行将会停止。此选项会导致执行继续，而不管是否有错误。

• --host=name, -h:指定连接主机。

• --html, -H:以 HTML 格式输出所有结果。有关该选项的更多信息，请参见“有用的 mysql 技巧”一节中的相应技巧。

• 当快速输入和执行查询时，经常会出现错误，导致恼人的哔哔声错误。使用此选项禁用声音。

• 许多查询产生的信息超过了一个屏幕所能容纳的范围。通过分配一个分页器，您可以告诉客户端一次显示一页结果。有效寻呼机的例子包括 Unix 命令more和less。目前，该命令仅在 Unix 平台上有效。您也可以在mysql客户端中使用\P命令设置一个寻呼机。

• --password, -p:指定密码。请注意，您不应该像用户名或主机一样在命令行上提供密码，而是应该等待随后的提示，这样密码就不会以纯文本的形式存储在您的命令历史记录中。

• --port=#, -P:指定主机连接端口。

• --protocol=name : MySQL 支持内存、管道、套接字、tcp 四种连接协议。使用此选项指定您想要使用的协议:

  • TCP 协议:当客户端和服务器驻留在两台不同的机器上时，默认情况下使用，并且需要端口 3306 才能正常工作(端口号可以用--port更改)。如果客户端和服务器驻留在不同的计算机上，您需要使用 TCP，尽管当所有通信都在本地进行时您也可以使用它。

  • 套接字文件(Socket files ):一个 Unix 特有的特性，它有助于两个不同程序之间的通信，当通信在本地进行时，它是默认的。

  • 共享内存:一种 Windows 独有的功能，它使用一个公共内存块来实现通信。

  • 命名管道(Named pipes ):一个仅在 Windows 上使用的功能，其功能类似于 Unix 管道。

$ mysql -u root -p -e "USE corporate; SELECT * from product;"

注意

默认情况下，不会启用上述两个特定于 Windows 的选项(TCP 是 Windows 上本地和远程通信的默认选项)。

• -- safe-updates , -U:使mysql忽略所有省略了WHERE子句的DELETE和UPDATE查询。这是防止意外批量删除或修改的特别有用的保护措施。有关这个选项的更多信息，请参阅“有用的 mysql 技巧”一节。

• -- skip-column-names:默认情况下，mysql在每个结果集的顶部包含包含列名的标题。您可以使用此选项禁止包含这些头。

• --tee=name:使mysql将所有命令和结果输出记录到name指定的文件中。这对于调试特别有用。在 MySQL 内部，您可以通过发出命令notee随时禁用日志记录，并可以在以后使用命令tee重新启用它。有关该选项的更多信息，请参见“有用的 mysql 技巧”一节中的相应技巧。

• --vertical、-E:使mysql以垂直格式显示所有查询结果。当您处理包含多列的表格时，这种格式通常更可取。有关该选项的更多信息，请参见“有用的 mysql 技巧”一节中的相应技巧。

• --xml、-X:将所有结果以 XML 格式输出。有关该选项的更多信息，请参见“有用的 mysql 技巧”一节中的相应技巧。

mysqladmin 客户端

mysqladmin客户机用于执行各种各样的管理任务，可能最显著的是创建和销毁数据库、监控服务器状态以及关闭 MySQL 服务器守护进程。像mysql一样，你需要传递必要的访问凭证来使用mysqladmin。

例如，您可以通过执行以下命令来检查所有服务器变量及其值

%>mysqladmin -u root -p variables
Enter password:
+-------------------------------------+
| Variable_name             | Value   |
+-------------------------------------+
| auto_increment_increment  |  1      |
| auto_increment_offset     |  1      |
| autocommit                | ON      |
…
| version_compile_os        | osx10.6 |
| wait_timeout              | 28800   |

如果您提供了有效的凭证，将会滚动一长串参数和相应的值。如果您想浏览结果，如果您使用的是 Linux，您可以通过管道将该输出发送到more或less，如果您使用的是 Windows，则发送到more。

mysqladmin 命令

虽然mysql本质上是一个自由格式的 SQL shell，允许 MySQL 识别任何 SQL 查询，但是mysqladmin的范围要有限得多，只能识别一组预定义的命令；这里介绍最常用的:

• create databasename:新建一个数据库，其名称由 databasename 指定。请注意，每个数据库必须拥有唯一的名称。尝试使用现有数据库的名称创建数据库将导致错误。

• drop databasename:删除一个现有的数据库，其名称由 databasename 指定。提交删除数据库的请求后，系统会提示您确认请求，以防止意外删除。

• extended-status:提供关于服务器状态的扩展信息。这与从mysql客户端中执行show status是一样的。

• flush-privileges:重新加载权限表。如果您使用的是GRANT和REVOKE命令，而不是使用 SQL 查询直接修改特权表，那么您不需要使用这个命令。

• kill id[,id2[,id N ]]:终止id、id2到id、N指定的进程。您可以使用processlist命令查看进程号。

• old-password new-password:使用 MySQL 4.1 之前的密码哈希算法，将-u指定的用户密码更改为new-password。

• password new-password:使用后 MySQL 4.1 密码哈希算法将-u指定的用户密码更改为new-password。

• ping:通过 pinged MySQL 服务器来验证它是否正在运行，就像 web 或邮件服务器可能被 ping 一样。

• processlist:显示所有正在运行的 MySQL 服务器守护进程列表。

• shutdown:关闭 MySQL 服务器守护进程。注意，您不能使用 mysqladmin 重启守护进程。相反，它必须使用第二十六章中介绍的机制重新启动。

• stat us:输出各种服务器统计数据，比如正常运行时间、执行的查询总数、打开的表、平均每秒查询数和运行的线程。

• variables:输出所有服务器变量及其对应的值。

• version:输出版本信息和服务器统计。

让我们考虑几个简单的例子。如果您想快速创建一个新的数据库，您可以使用create命令:

$ mysqladmin -u -p create dev_gamenomad_com
Enter password:

您可以使用processlist命令查看正在运行的 MySQL 进程列表:

$ mysqladmin -u root -p processlist
Enter password:
+----+-----+----------+----------------+--------+------+-----+------------+
| Id | User| Host     |db              | Command| Time |State| Info       |
+----+-----+----------+----------------+--------+------+-----+------------+
| 387| root| localhost|local_apress_mis| Sleep  | 7071 |     |            |
| 401| root| localhost|                | Query  | 0    |     | show                                                                processlist|
+----+-----+----------+----------------+--------+------+-----+------------+

尽管有太多基于 GUI 的管理工具，但我倾向于将大部分 MySQL 管理时间花在mysql客户机上，用它来完成大多数管理任务。然而，当需要快速查看系统状态或配置信息时，我会使用mysqladmin(分别通过extended-status和variables命令)，将这些命令与 Unix 的grep和less命令结合起来。在 Windows 上，可以在 Windows 7 的 findstr 或 PowerShell 中找到类似的功能。

其他有用的客户端

本节涵盖了 MySQL 的其他几个本地客户端。像mysql和mysqladmin客户端一样，本节介绍的所有实用程序都可以通过--help选项调用。

注意

两个非常有用的导出数据的客户端是mysqlhotcopy和mysqldump；然而，我将放弃在这里介绍它们，把这些介绍留到第三十五章中，在那里我将全面概述 MySQL 的各种数据导入和导出功能。

MySQL show(MySQL 显示)

mysqlshow实用程序提供了一种便捷的方式来快速查看给定数据库服务器上存在哪些数据库、表和列。其用法语法如下:

mysqlshow [options] [database [table [column]]]

例如，假设您想要查看所有可用数据库的列表:

%>mysqlshow -u root -p
Enter password:
+------------------------------+
|          Databases           |
+------------------------------+
| information_schema           |
| employees                    |
| mysql                        |
| test                         |
+------------------------------+

要查看特定数据库(如employees)中的所有表，请使用以下命令:

%>mysqlshow -u root -p employees
Enter password:
Database: employees
+--------------+
|    Tables    |
+--------------+
| departments  |
| dept_emp     |
| dept_manager |
| employees    |
| salaries     |
| titles       |
+--------------+

要查看特定表中的所有列，如employee数据库的salaries表，请使用以下命令:

%>mysqlshow -u root -p employees salaries
Enter password:
Database: employees  Table: salaries
+---------+------+---------+---+----+-------+-----+----------------+------+
| Field   |Type  |Collation|Null|Key|Default|Extra|Privileges      |Comment|
+---------+------+---------+----+---+-------+-----+----------------+------+
| emp_no  |int(11)|         |NO  |PRI|       |     |select, insert,                                                  update,references|      |
| salary  |int(11)|         |NO  |   |       |     |select, insert,                                                  update,references|      |
| from_date|date  |         |NO  |PRI|       |     |select,insert,                                                  update,references|      |
| to_date |date   |         |NO  |   |       |     |select,insert,                                                  update,references|      |
+-------+---------+---------+----+---+-------+-----+---------------+------+

请注意，显示的内容完全取决于提供的凭据。在前面的例子中，使用了root用户，这意味着所有信息都由用户处理。但是，其他用户可能没有如此广泛的访问权限。因此，如果您对调查所有可用的数据结构感兴趣，请使用root用户。

有用的 GUI 客户端程序

认识到不是所有的用户都特别喜欢从命令行工作，许多公司和开源团队提供了奇妙的、基于图形的数据库管理解决方案。几年来，MySQL 团队实际上维护了几个不同的基于 GUI 的管理产品；然而，它们最终被合并到一个名为 MySQL Workbench 的项目中。MySQL Workbench 旨在成为管理 MySQL 服务器所有方面的一站式商店，包括模式、用户和表数据。

MySQL Workbench 可以在所有标准平台上使用，包括 Linux、OS X 和 Windows。如果您想自己构建，也可以使用源代码。前往 https://dev.mysql.com/downloads/tools/workbench 获取适合您平台的版本。

一旦安装完毕，我建议花些时间探索 MySQL Workbench 的许多特性。我发现基于 GUI 的模式设计和正向工程特性是必不可少的(图 27-1 )，因为它允许您使用方便的点击式界面设计和维护数据库模式，而不是手工编写模式命令。

图 27-1

MySQL 工作台

phpMyAdmin

phpMyAdmin 是一个用 PHP 编写的基于 web 的 MySQL 管理应用，被成千上万的开发人员使用，实际上是全球虚拟主机提供商的主要产品。它自 1998 年以来一直在积极开发，但由于热情的开发团队和用户社区，它的功能也很丰富。作为这款产品的长期用户，很难想象没有它会怎样。

phpMyAdmin 提供了许多引人注目的特性:

• 管理员可以完全控制用户权限、密码和资源使用，以及创建、删除甚至复制用户帐户。

• 实时界面可用于查看正常运行时间信息、查询和服务器流量统计、服务器变量和正在运行的进程。

• 来自世界各地的开发人员已经将 phpMyAdmin 的界面翻译成 50 多种语言，包括英语、中文(繁体和简体)、阿拉伯语、法语、西班牙语、希伯来语、德语和日语。

• phpMyAdmin 提供了一个高度优化的点击式界面，大大降低了用户引发错误的可能性。

图 27-2

在 phpMyAdmin 中查看数据库

• phpMyAdmin 是基于浏览器的，允许您从任何可以访问 Web 的地方轻松管理远程 MySQL 数据库。还透明地支持 SSL，如果您的服务器提供这一特性，则允许加密管理。用于管理数据库表的界面截图如图 27-2 所示。

phpMyAdmin 是在 GNU 通用公共许可证下发布的。phpMyAdmin 的官方网站， http://phpmyadmin.net ，提供源代码下载、新闻、邮件列表、现场演示等等。

clinets 到 MySQL 和 MariaDB 还有很多其他选择；Webyog/SQLyog，HeidiSQL，dbForge Studio for MariaDB 仅举几例。像 PHP Storm 这样的现代编辑器也支持直接的数据库连接，这在处理 SQL 文件时非常方便。

摘要

本章介绍了 MySQL 的众多客户端，重点介绍了mysql和mysqladmin。还介绍了几种最流行的基于 GUI 的管理解决方案。因为管理是维护健康的数据库服务器的一个关键方面，所以考虑尝试所有这些方法，以确定哪种方法最适合您的特定数据库管理情况。

下一章将讨论 MySQL 的另一个关键方面:表结构和数据类型。您将了解各种表类型以及支持的数据类型和属性；您还将看到许多关于如何创建、修改和使用数据库、表和列的示例。

Footnotes 1

虽然缺少外壳可能看起来很奇怪，但 mysql 和 mysqladmin 确实是这些客户端的正式名称。

二十五、MySQL 存储引擎和数据类型

花时间正确设计项目的表结构是项目成功的关键。忽略这一点不仅会对存储需求造成可怕的后果，还会影响应用的性能、可维护性和数据完整性。在这一章中，你将更加熟悉 MySQL 表设计的许多方面。根据其结论，您将熟悉以下主题:

• MySQL 的关键存储引擎，即 ARCHIVE、BLACKHOLE、CSV、EXAMPLE、FEDERATED、InnoDB、MEMORY(原 HEAP)、MERGE、MyISAM 的用途、优缺点及相关配置参数。

• MySQL 支持的数据类型的用途和范围。为了便于以后参考，这些数据类型分为三类:日期和时间、数字和文本。

• MySQL 的表属性，用于进一步修改数据列的行为。

• 用于创建、修改、导航、查看和更改数据库和表的 MySQL 命令。

存储引擎

关系数据库表是用于存储和组织信息的数据结构。你可以把一个表格想象成由行和列组成的网格，就像一个电子表格。例如，您可以设计一个用于存储员工联系信息的表，该表可能由五列组成:员工 ID、名字、姓氏、电子邮件地址和电话号码。对于一个由四名雇员组成的组织，这个表将由四行组成，即记录。尽管这个例子过于简单，但它清楚地描述了表的用途:作为一种易于访问的工具来存储一般数据。

然而，数据库表还有许多其他用途，其中一些相当复杂。例如，数据库也通常用于存储交易信息。一个事务是一组任务，共同被认为是一个工作单元。如果所有的单元任务都成功，那么表的改变将被执行，或者提交。如果任何一个任务失败，那么前面和正在进行的任务的所有结果都必须被取消，或者回滚。您可能会将事务用于用户注册、银行业务或电子商务等过程，在这些过程中，必须正确执行所有步骤以确保数据一致性。正如您所想象的，由于表中必须包含额外的特性，这样的功能需要一些开销。

注意

MySQL 的事务特性在第三十四章中介绍。

有些表根本不打算存储任何长期信息，实际上完全是在服务器的 RAM 或一个特殊的临时文件中创建和维护的，以确保高性能，但存在高波动性的风险。其他表的存在只是为了简化对一组相同表的维护和访问，为同时与所有表进行交互提供一个单一的界面。还存在其他特殊用途，但重点已经提出:MySQL 支持许多类型的表，也称为存储引擎，每种都有其特定的用途、优点和缺点。本节介绍 MySQL 支持的存储引擎，概述每个引擎的用途、优点和缺点。与其按字母顺序介绍存储引擎，不如从最常用的存储引擎(如 InnoDB)开始介绍，最后介绍用于更具体用途的存储引擎:

• InnoDB

• 我的天

• 记忆

• 合并

• 联邦的

• 档案馆

• 战斗支援车

• 例子

• 黑洞

存储引擎演示之后是常见问题解答部分，用于解决与存储引擎相关的其他问题。

InnoDB

InnoDB 是一个健壮的事务存储引擎，在 GNU 通用公共许可证(GPL)下发布，十多年来一直在积极开发中。InnoDB 为用户提供了处理超大型数据存储的强大解决方案。从 3.23.34a 版开始，MySQL 用户就可以使用它了，事实证明，对于事务性应用来说，它是一个非常流行和有效的解决方案，从 4.0 版开始，默认情况下就启用了支持。

尽管 InnoDB 通常与其他存储引擎放在一起，正如这里所做的那样，但它实际上是一个完整的数据库后端。InnoDB 表资源使用专用缓冲区进行管理，可以像控制任何其他 MySQL 配置参数一样对其进行控制。InnoDB 还通过行级锁定和外键约束为 MySQL 带来了其他巨大的进步。

InnoDB 表非常适合以下场景，其中包括:

• 更新密集型表:InnoDB 存储引擎特别擅长处理多个同步更新请求。

• 事务:InnoDB 存储引擎是唯一支持事务的标准 MySQL 存储引擎，这是管理财务或用户注册信息等敏感数据的必备功能。

• 自动崩溃恢复:与其他存储引擎不同，InnoDB 表能够从崩溃中自动恢复。虽然 MyISAM 表也可以在崩溃后修复，但这个过程可能会花费很长时间。还提供了一个名为 Aria 的 MyISAM 崩溃安全版本。

我的天

MyISAM 曾经是 MySQL 的默认存储引擎。它解决了其前身(ISAM)的一些缺陷。首先，MyISAM 表是独立于操作系统的，这意味着您可以轻松地将它们从 Windows 服务器移植到 Linux 服务器。此外，MyISAM 表通常能够存储更多的数据，但其存储空间比旧表要少。MyISAM 表还可以方便地使用许多数据完整性和压缩工具，这些工具都与 MySQL 捆绑在一起。

MyISAM 表不能处理事务，当性能成为问题时，它比 InnoDB 更受青睐。随着时间的推移，InnoDB 的性能有所提高，在大多数情况下这不再是一个问题。当应用于以下场景时，MyISAM 存储引擎表现得尤为出色:

• 选择密集型表:MyISAM 存储引擎筛选大量数据的速度非常快，即使在高流量环境中也是如此。

• 追加密集型表 : MyISAM 的并发插入特性允许同时选择和插入数据。例如，MyISAM 存储引擎非常适合管理邮件或 web 服务器日志数据。

米沙姆静态

如果所有表列的大小都是静态的(即不使用xBLOB、xTEXT或VARCHAR数据类型)，MySQL 会自动使用静态 MyISAM 变量。这种类型的表性能特别高，因为维护和访问以预定义格式存储的数据所需的开销很低，更不用说它最不可能因数据损坏而失败。但是，这种优势是以空间为代价的，因为每一列都需要为每一列分配最大的空间，而不管该空间实际上是否被使用。以两个用于存储用户信息的完全相同的表为例。一个表authentication_static使用静态的CHAR数据类型来存储用户的用户名和密码:

CREATE TABLE authentication_static (
   id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   username CHAR(15) NOT NULL,
   pswd CHAR(15) NOT NULL,
   PRIMARY KEY(id)
   ) ENGINE=MyISAM;

另一个表 authentication_dynamic 使用动态 VARCHAR 数据类型:

CREATE TABLE authentication_dynamic (
   id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   username VARCHAR(15) NOT NULL,
   pswd VARCHAR(15) NOT NULL,
   PRIMARY KEY(id)
   ) ENGINE=MyISAM;

因为authentication_static只使用静态字段，所以它自动采用 MyISAM-static 形式(尽管甚至在使用数据类型如VARCHAR、NUMERIC和DECIMAL时也可以强制 MySQL 使用静态形式)，而另一个表authentication_dynamic采用 MyISAM-dynamic 形式(下一节将介绍)。现在在每个中插入一行:

INSERT INTO authentication_static SET id=NULL, username="jason", pswd="secret";
INSERT INTO authentication_dynamic SET id=NULL, username="jason", pswd="secret";

将这一行插入每一行将导致authentication_static比authentication_dynamic大 60%多一点(33 字节对 20 字节)，因为静态表总是占用表定义中指定的空间，而动态表只占用插入数据所需的空间。但是，不要把这个例子看作是对仅仅坚持 MyISAM-dynamic 格式的有力支持。下一节讨论此存储引擎的特征，包括其缺点。

米沙姆动态

如果有一个表列被定义为动态的，MySQL 就会自动使用动态变量(使用xBLOB、xTEXT或VARCHAR)。尽管 MyISAM-dynamic 表比它的静态表占用更少的空间，但是节省空间会带来性能上的损失。如果一个字段的内容发生变化，那么这个位置可能需要移动，从而导致碎片。随着数据集变得越来越碎片化，数据访问性能将会受到相应的影响。有两种方法可以解决这个问题:

• 尽可能使用静态数据类型。

• 定期使用OPTIMIZE TABLE语句，该语句对表进行碎片整理，并恢复由于表更新和删除而随着时间推移而丢失的空间。

米色压缩

有时，您会创建在应用的整个生命周期中都是只读的表。如果是这种情况，您可以通过使用 myisampack 实用工具将它们转换为 MyISAM 压缩的表，从而显著减小它们的大小。给定某些硬件配置(例如，快速处理器和慢速硬盘)，性能节省可能是显著的。

记忆

MySQL 的内存存储引擎的创建只有一个目标:速度。为了获得最快的响应时间，逻辑存储介质是系统内存。虽然将表数据存储在内存中确实提供了令人印象深刻的性能，但是请记住，如果 MySQL 守护进程崩溃，所有内存数据都将丢失。

注意

从 4.1 版开始，这个存储引擎从堆重命名为内存。然而，因为这个存储引擎长期以来一直是 MySQL 的一部分，所以在文档中您仍然会经常看到它的旧名称。此外，堆仍然是内存的同义词。

这种速度的提高是以几个缺点为代价的。例如，内存表不支持VARCHAR、BLOB或TEXT数据类型，因为这种表类型是以固定记录长度格式存储的。当然，您应该记住，内存表是为特定的范围设计的，而不是为数据的长期存储设计的。当您的数据属于以下情况时，您可以考虑使用内存表:

• 可忽略的:与可用的系统内存相比，目标数据相对较小，并且访问非常频繁。请记住，将数据存储在内存中可以防止内存被用于其他目的。注意，可以用参数max_heap_table_size控制内存表的大小。该参数作为资源保护措施，对内存表的大小设置了最大限制。

• 瞬态:目标数据只是暂时需要，在其生命周期内必须立即可用。

• 相对无关紧要:存储在内存表中的数据突然丢失不会对应用服务产生任何实质性的负面影响，当然也不会对数据完整性产生长期影响。

哈希索引和 B 树索引都受支持。B 树索引相对于散列的优势在于可以使用部分和通配符查询，并且可以使用诸如<、>和>=之类的操作符来促进数据挖掘。

您可以在创建表时指定与USING子句一起使用的版本。以下示例在username列上声明了一个散列索引:

CREATE TABLE users (
   id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   username VARCHAR(15) NOT NULL,
   pswd VARCHAR(15) NOT NULL,
   INDEX USING HASH (username),
   PRIMARY KEY(id)
   ) ENGINE=MEMORY;

相比之下，以下示例在同一列上声明了一个 B 树索引:

CREATE TABLE users (
   id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
   username VARCHAR(15) NOT NULL,
   pswd VARCHAR(15) NOT NULL,
   INDEX USING BTREE (username),
   PRIMARY KEY(id)
   ) ENGINE=MEMORY;

合并

MyISAM 还提供了一个额外的变体，虽然没有其他的那么常用，但是在某些情况下还是很有用的。这种变体称为合并表，实际上是相同的 MyISAM 表的集合。这为什么有用？考虑到数据库经常被用于存储特定时间的数据:销售信息、服务器日志和航班时刻表都是首选。然而，这样的数据存储很容易变得过大并且非常笨拙。因此，一种常见的存储策略是将数据分成多个表，每个表的名称对应一个特定的时间段。例如，可能会使用 12 个相同的表来存储服务器日志数据，每个表都有一个对应于一年中每个月的名称。但是，基于分布在所有 12 个表中的数据的报告是必要的，这意味着需要编写和更新多表查询来反映这些表中的信息。与其编写这种可能容易出错的查询，不如将这些表合并在一起，使用单个查询来代替。稍后可以删除合并表，而不会影响原始数据。

联邦的

许多环境倾向于在一台服务器上运行 Apache、MySQL 和 PHP。实际上，这对于许多目的来说都很好，但是如果您需要从许多不同的 MySQL 服务器上聚集数据，其中一些服务器位于网络之外或者完全属于另一个组织，该怎么办呢？因为很久以前就可以连接到远程 MySQL 数据库服务器(更多细节见第二十四章)，所以这并不是一个问题；然而，管理到每个独立服务器的连接的过程可能很快变得乏味。为了缓解这个问题，您可以使用联邦存储引擎创建一个指向远程表的本地指针，该引擎从 MySQL 5.0.3 开始提供。这样做允许您执行查询，就像表驻留在本地一样，省去了分别连接到每个远程数据库的麻烦。

注意

默认情况下不会安装联邦存储引擎，所以您需要使用选项--with-federated-storage-engine来配置 MySQL，以便利用它的特性。此外，MySQL 服务器必须使用- federated 选项启动。

因为创建联邦表的过程与创建其他表的过程有些不同，所以需要一些额外的解释。如果您不熟悉一般的表创建语法，可以在继续之前直接跳到“使用数据库和表”一节。假设一个名为 products 的表驻留在远程服务器(称为服务器 A)上的公司数据库中。该表如下所示:

CREATE TABLE products (
   id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   sku CHAR(8) NOT NULL,
   name VARCHAR(35) NOT NULL,
   price DECIMAL(6,2)
) ENGINE=MyISAM;

假设您想从其他服务器(称为服务器 B)访问这个表。为此，在服务器 B 上创建一个相同的表结构，惟一的区别是表引擎类型应该是 FEDERATED 而不是 MyISAM。此外，必须提供连接参数，这允许服务器 B 与服务器 A 上的表进行通信:

CREATE TABLE products (
   id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   sku CHAR(8) NOT NULL,
   name VARCHAR(35) NOT NULL,
   price DECIMAL(6,2)
   ) ENGINE=FEDERATED
  CONNECTION='mysql://remoteuser:secret@192.168.1.103/corporate/products';

连接字符串应该相当容易理解，但是有几点值得注意。首先，由用户名remoteuser和密码secret标识的用户必须驻留在服务器 A 上的mysql数据库中。其次，由于该信息将通过可能不安全的网络传输到服务器 A，第三方不仅可能捕获认证变量，还可能捕获表数据。参见第二十六章，了解如何降低第三方获取这些数据的可能性，以及在万一发生的情况下，如何限制潜在影响。

创建后，您可以通过访问服务器 b 上的products表来访问服务器 A 的products表。此外，如果连接字符串中分配的用户拥有必要的权限，也可以添加、修改和删除驻留在这个远程表中的数据。

档案馆

即使现在有了低成本、高容量的存储设备，银行、医院和零售商等组织也必须特别注意以尽可能高效的方式存储大量数据。因为这些数据通常必须保留很长一段时间，即使可能很少被访问，压缩这些数据是有意义的，只在必要时才解压缩。为了满足这些目的，在版本 4.1.3 中添加了归档存储引擎。

归档存储引擎通过使用 zlib 压缩库( https://www.zlib.net )极大地压缩在这种类型的表中找到的任何数据，并在请求记录时动态地解压缩它。除了选择记录之外，还可以插入记录，这在将老化数据迁移到归档表中变得可行时是必要的。但是，不可能删除或更新存储在这些表中的任何数据。

请注意，存储在存档表中的任何数据都不会被索引，这意味着SELECT操作可能相当低效。如果出于某种原因，您需要对归档表执行扩展分析，那么将该表转换为 MyISAM 并重新创建必要的索引可能是有意义的。有关如何在引擎之间转换的信息，请参见本章后面的“存储引擎常见问题”。

战斗支援车

CSV 存储引擎以逗号分隔的格式存储表格数据，类似于许多应用(如 OpenOffice 和 Microsoft Office)支持的格式。

虽然您可以像访问和操作任何其他类型的表一样访问和操作 CSV 表，例如 MyISAM，但是 CSV 表实际上是文本文件。这有一个有趣的含义，即您实际上可以将一个现有的 CSV 文件复制到 MySQL 的指定数据文件夹中的相应数据文件(标有.csv扩展名)上。此外，鉴于 CSV 文件的特殊格式，不可能利用典型的数据库特性，如索引。

例子

因为 MySQL 的源代码是免费的，你可以自由地修改它，只要你遵守它各自的许可条款。意识到开发人员可能希望创建新的存储引擎，MySQL 提供了示例存储引擎作为理解如何创建这些引擎的基本模板。

黑洞

从 MySQL 4.1.11 开始，黑洞存储引擎的运行方式就像 MyISAM 引擎一样，只是它不会存储任何数据。您可以使用这个引擎来衡量日志记录所带来的开销，因为即使不存储数据，也仍然可以记录查询。

小费

黑洞存储引擎默认是不启用的，所以你需要在配置时包含选项--with-blackhole-storage-engine来使用它。

存储引擎常见问题

围绕与存储引擎相关的各种问题，经常会有一些混淆。因此，本节专门解决有关存储引擎的常见问题。

我的服务器上有哪些存储引擎可用？

要确定 MySQL 服务器可以使用哪些引擎，请执行以下命令:

mysql>SHOW ENGINES;

因为默认情况下有几个引擎是不启用的，所以如果您想要的引擎没有在列表中找到，您可能需要用一个启用该引擎的标志来重新配置 MySQL。

对于 CentOS 7 平台上的 MariaDB，列表如下所示:

+--------------------+---------+------------------------------------------+
| Engine             | Support | Comment                                  |
+--------------------+---------+------------------------------------------+
| CSV                | YES     | CSV storage engine                       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables    |
| MEMORY             | YES     | Hash based, stored in memory, useful                                  for temporary tables                     |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you                                  write to it disappears)                 |
| MyISAM             | YES     | MyISAM storage engine                    |
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions,                                  row-level locking, and foreign keys      |
| ARCHIVE            | YES     | Archive storage engine                   |
| FEDERATED          | YES     | FederatedX pluggable storage engine      |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                       |
| Aria               | YES     | Crash-safe tables with MyISAM heritage   |
+--------------------+---------+------------------------------------------+

该列表没有显示输出的最后三列。注意，InnoDB 是 Linux 上的默认引擎。InnoDB 的版本由一家名为 Percona 的公司提供。他们对 InnoDB 的原始版本进行了增强。

我如何利用 Windows 上的存储引擎？

默认情况下，当运行 MySQL 5.0 或更高版本时，归档、黑洞、CSV、示例、联邦、InnoDB、内存、合并和 MyISAM 存储引擎在 Windows 上可用。注意，当使用 MySQL 配置向导安装 MySQL 时，InnoDB 是默认的(参见第二十三章)。要使用其他受支持的类型，您需要安装 Max 版本或从源代码构建 MySQL。

在同一个数据库中使用多个存储引擎有错吗？

一点也不。事实上，除非您正在处理一个特别简单的数据库，否则您的应用很可能会从使用多个存储引擎中受益。仔细考虑数据库中每个表的用途和行为并相应地选择合适的存储引擎总是一个好主意。不要偷懒，只使用默认的存储引擎；从长远来看，这可能会对应用的性能产生不利影响。

如何在创建时指定存储引擎或在以后更改它？

您可以在创建时通过传递属性TYPE=TABLE_TYPE . You来选择性地分配存储引擎，稍后可以使用ALTER命令或使用 MySQL 发行版附带的mysql_convert_table_format脚本来转换一个表，或者使用 manu GUI 客户端中的一个提供了一种简单的方法来实现这一点。

我需要速度！最快的存储引擎是什么？

因为内存表存储在内存中，所以它们提供了极快的响应时间。但是，请记住，存储在内存中的任何东西都是非常不稳定的，如果服务器或 MySQL 崩溃或关闭，这些东西就会消失。尽管内存表确实有重要的用途，但是如果速度是您的目标，您可能需要考虑其他优化途径。您可以从花时间正确设计您的表开始，始终选择最佳的数据类型和存储引擎。此外，要努力优化您的查询和 MySQL 服务器配置，当然永远不要吝啬服务器硬件。此外，您可以利用 MySQL 的特性，比如查询缓存。

数据类型和属性

对放入 MySQL 表中每一列的数据进行严格的控制，对于数据驱动的应用的成功至关重要。例如，您可能希望确保该值不超过最大限制，不超出特定格式的界限，或者甚至将允许的值约束到预定义的集合中。为了帮助完成这项任务，MySQL 提供了一组数据类型，可以将它们分配给表中的每一列。每种方法都强制数据符合该数据类型固有的一组预定规则，例如大小、类型(例如字符串、整数或小数)和格式(例如确保它符合有效的日期或时间表示)。

通过包含属性，可以进一步调整这些数据类型的行为。本节介绍 MySQL 支持的数据类型和许多常用的属性。因为许多数据类型支持相同的属性，所以属性定义不会在每个数据类型部分重复；相反，属性定义分组在“数据类型”一节后面的“数据类型属性”标题下。

数据类型

本节介绍 MySQL 支持的数据类型，提供关于每种数据类型的名称、用途、格式和范围的信息。为了便于以后参考，它们分为三类:日期和时间、数字和字符串。

日期和时间数据类型

许多类型可用于表示基于时间和日期的数据。

日期

日期数据类型负责存储日期信息。虽然 MySQL 以标准的YYYY-MM-DD格式显示DATE值，但是可以使用数字或字符串插入这些值。例如，20100810和2010-08-10都将被接受为有效输入。范围是1000-01-01到9999-12-31。

注意

对于所有日期和时间数据类型，MySQL 将接受任何类型的非字母数字分隔符来分隔各种日期和时间值。比如20080810、2008*08*10、2010、08、10、2010!08!10就 MySQL 而言都是一样的。

DATETIME

DATETIME数据类型负责存储日期和时间信息的组合。与DATE、DATETIME一样，值以标准格式存储，YYYY-MM-DD HH:MM:SS；可以使用数字或字符串插入值。例如，20100810153510和2010-08-10 15:35:10都将被接受为有效输入。DATETIME的范围是1000-01-01 00:00:00到9999-12-31 23:59:59。

时间

TIME数据类型负责存储时间信息，并支持足够大的范围，不仅可以表示标准和军事风格的时间格式，还可以表示扩展的时间间隔。这个范围是–838:59:59到838:59:59。

时间戳[默认][更新时]

TIMESTAMP数据类型与DATETIME不同，MySQL 的默认行为是每当执行影响它的INSERT或UPDATE操作时，自动将其更新为当前日期和时间。TIMESTAMP值以HH:MM:SS格式显示，并且，像DATE和DATETIME数据类型一样，您可以使用数字或字符串赋值。TIMESTAMP的范围是1970-01-01 00:00:01到2037-12-31 23:59:59。它的存储要求是 4 字节。

警告

当无效值被插入到DATE、DATETIME、TIME或TIMESTAMP列时，它会显示为一串根据数据类型的规范格式化的零。

TIMESTAMP列长期以来一直是开发人员困惑的来源，因为如果没有正确定义，它可能会出现意想不到的行为。为了消除一些混淆，这里提供了一系列不同的定义和相应的解释。对于表中定义的第一个时间戳，现在可以分配默认值。您可以为它指定值 CURRENT_TIMESTAMP 或某个常量值。将其设置为常量意味着无论何时更新行，时间戳都不会改变。

• TIMESTAMP DEFAULT 20080831120000:从版本 4.1.2 开始，表中定义的第一个TIMESTAMP将接受默认值。

• TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP:表中定义的第一个时间戳列采用当前时间戳的值，并且每次更新行时都更新为当前时间戳。

• TIMESTAMP:当表格中的第一个TIMESTAMP列如此定义时，等同于同时用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP定义。

• TIMESTAMP DEFAULT CURRENT_TIMESTAMP:表中定义的第一个TIMESTAMP列采用当前时间戳的值，但它不会在每次更新行时更新为当前时间戳。

• TIMESTAMP ON UPDATE CURRENT_TIMESTAMP:当插入行时，表中定义的第一个TIMESTAMP列被赋予0，当行被更新时，它被更新为当前的时间戳。

年份[(2|4)]

YEAR数据类型负责存储特定于年份的信息，根据上下文支持多个范围:

• 两位数 : 1至99。范围在1和69之间的值被转换成范围在2001到2069之间的值，而范围在70和99之间的值被转换成范围在1970到1999之间的值。

• 四位数字 : 1901到2155。

• 两位数字符串 : "00"到"99."范围在"00"到"69"之间的值被转换为范围在"2000"到"2069,"之间的值，而范围在"70"到"99"之间的值被转换为范围在"1970"到"1999."之间的值

• 四位数字 字符串 : "1901"到"2155."

数字数据类型

有许多类型可以用来表示数字数据。

注意

许多数字数据类型允许您约束最大显示大小，在下面的定义中，由类型名称后面的M参数表示。许多浮点类型允许您指定小数点后的位数，由参数D表示。这些参数以及相关的属性都是可选的，用方括号表示。

布尔值，布尔值

BOOL和 BOOLEAN 只是 TINYINT(1)的别名，用于 0 或 1 的赋值。此数据类型是在 4.1.0 版中添加的。

比什特(m)

BIGINT数据类型提供了 MySQL 最大的整数范围，支持从–9,223,372,036,854,775,808到9,223,372,036,854,775,807的有符号范围和从0到18,446,744,073,709,551,615的无符号范围。

INT[(M)][无符号][零填充]

INT数据类型提供了 MySQL 的第二大整数范围，支持从–2147483648 到 2147483647 的有符号范围和从 0 到 4294967295 的无符号范围。

中值[(M)][无符号][零填充]

MEDIUMINT数据类型提供了 MySQL 的第三大整数范围，支持从–8,388,608到8,388,607的有符号范围和从0到16,777,215的无符号范围。

SMALLINT[(M)][无符号][零填充]

SMALLINT数据类型提供了 MySQL 的第四大整数范围，支持从–32,768到32,767的有符号范围和从0到65,535的无符号范围。

TINYINT[(M)][无符号][零填充]

TINYINT数据类型是 MySQL 的最小整数范围，支持从–128到127的有符号范围和从0到255的无符号范围。

十进制([M[，D]])[无符号][零填充]

DECIMAL 数据类型是存储为字符串的浮点数，支持有符号范围–1.7976931348623157E+308到–2.2250738585072014E–308和无符号范围2.2250738585072014E–308到1.7976931348623157E+308。在确定数字的总大小时，小数点和减号将被忽略。

DOUBLE([M，D])[无符号][零填充]

DOUBLE数据类型为双精度浮点数，支持有符号范围–1.7976931348623157E+308到–2.2250738585072014E–308和无符号范围2.2250738585072014E–308到1.7976931348623157E+308。

FLOAT([M，D])[无符号][零填充]

这个FLOAT数据类型变体是 MySQL 的单精度浮点数表示，支持从–3.402823466E+38到–1.175494351E–38的有符号范围和从1.175494351E–38到3.402823466E+38的无符号范围。

浮点(精度)[无符号][零填充]

这个FLOAT数据类型变量是为了 ODBC 兼容性而提供的。单精度的精度范围在1到24之间，双精度的精度范围在25到53之间。该范围与前面FLOAT定义中定义的范围相同。

字符串数据类型

许多类型可用于表示字符串数据。

[国家]字符(长度)[二进制| ASCII | UNICODE]

CHAR数据类型提供 MySQL 的固定长度字符串表示，支持最大 255 个字符的长度。如果插入的字符串没有占据所有的长度空间，剩余的空间将由空格填充。检索时，这些空格将被省略。如果Length是一个字符，用户可以省略长度参考，只需使用CHAR。您还可以结合NOT NULL属性指定一个零长度的CHAR，这将只允许NULL或""。出于兼容性的原因，可以使用NATIONAL属性，因为这是 SQL-99 指定应该为列使用默认字符集的方式，MySQL 默认已经这样做了。提供BINARY属性会导致该列中的值以区分大小写的方式排序；省略它会导致它们以不区分大小写的方式排序。

如果Length大于 255，该列将自动转换为能够存储由提供的长度指定的值的最小的TEXT类型。同样从版本 4.1.0 开始，包含ASCII属性将导致对列应用 Latin1 字符集。最后，从版本 4.1.1 开始，包含UNICODE属性将导致对列应用 ucs2 字符集。

[国家] VARCHAR(长度)[二进制]

VARCHAR数据类型是 MySQL 的变长字符串表示，从 5.0.3 版本开始支持 0 到 65535 个字符的长度；从版本 4.0.2 开始为 0 到 255 个字符；以及 4.0.2 版之前的 1 到 255 个字符。出于兼容性的原因，可以使用NATIONAL属性，因为这是 SQL-99 指定应该为列使用默认字符集的方式(MySQL 默认已经这样做了)。提供BINARY属性会导致该列中的值以区分大小写的方式排序；省略它会导致它们以不区分大小写的方式排序。

从历史上看，VARCHAR不会存储任何尾随空格；但是，从版本 5.0.3 开始，出于符合标准的原因，它们被存储起来。

肺部出血

LONGBLOB数据类型是 MySQL 最大的二进制字符串表示，支持的最大长度为 4，294，967，295 个字符。

长文本

LONGTEXT 数据类型是 MySQL 最大的非二进制字符串表示，支持的最大长度为 4，294，967，295 个字符。

中庸之道

MEDIUMBLOB数据类型是 MySQL 的第二大二进制字符串表示，最多支持 16，777，215 个字符。

中文字

MEDIUMTEXT数据类型是 MySQL 的第二大非二进制文本字符串，能够存储的最大长度为 16，777，215 个字符。

一滴

BLOB数据类型是 MySQL 的第三大二进制字符串表示，支持的最大长度为 65，535 个字符。

文本

TEXT数据类型是 MySQL 的第三大非二进制字符串表示，支持的最大长度为 65，535 个字符。

丁丁历险记

TINYBLOB数据类型是 MySQL 最小的二进制字符串表示，支持最大 255 个字符的长度。

微小文本

TINYTEXT数据类型是 MySQL 最小的非二进制字符串表示，支持的最大长度为 255 个字符。

枚举("成员 1 "，"成员 2 "，"成员 65，535 ")

ENUM数据类型提供了一种方法，用于存储从一个预定义的组中选择的最多一个成员，该组包含最多 65，535 个不同的成员。成员的选择仅限于列定义中声明的那些成员。如果列声明包含属性NULL，那么NULL将被认为是一个有效值，并且将是默认值。如果声明了NOT NULL，列表的第一个成员将是默认的。

SET("成员 1 "，"成员 2 "，"成员 64 ")

SET数据类型提供了一种方法来指定从一个最多包含 64 个成员的预定义组中选择的零个或多个值。值的选择仅限于列定义中声明的值。存储要求是 1、2、3、4 或 8 个值，具体取决于成员的数量。您可以使用此公式确定确切的要求:( N +7)/8，其中 N 是集合大小。

空间数据类型

空间数据类型是具有多个标量值的复杂数据类型。例如，由两个值定义的点或具有多个值的面，这些值描述了面中每个点的 x 和 y 坐标。支持的空间数据类型有GEOMETRY、POINT、LINESTRING和POLYGON。这些类型可以存储每种类型的单个值。还提供了一组可以存储值集合的空间数据类型。这些被称为MULTIPOINT、MULTILINESTRING、MULTIPOLYGON和GEOMETRYCOLLECTION。详见 https://dev.mysql.com/doc/refman/5.7/en/spatial-types.html 。

JSON 数据类型

JSON 是 JavaScript 对象的文本表示，它可以存储在字符串列中，但是在搜索时使用字符串列有一些限制。当插入或更新数据时，本机JSON列类型执行验证。可以选择 JSON 对象的一部分，或者选择 JSON 对象具有特定值的行。更多信息可以在这里找到 https://dev.mysql.com/doc/refman/5.7/en/json.html 。

JSON数据类型允许您在数据库、PHP 脚本和 JavaScript 前端应用中使用相同的对象格式。

数据类型属性

虽然这个列表并不详尽，但是本节介绍了您最常用的属性，以及将在本书剩余部分使用的属性。

自动增量

AU TO_INCREMENT 属性去掉了许多数据库驱动的应用中所必需的逻辑层次:为新插入的行分配唯一整数标识符的能力。将该属性分配给列将导致最后插入的 ID +1 分配给每个新插入的行。

MySQL 要求将AUTO_INCREMENT属性与指定为主键的列结合使用。此外，每个表只允许有一个 AUTO_INCREMENT 列。下面是一个AUTO_INCREMENT列赋值的例子:

id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY 

KEY

二进制的

BINARY属性仅与CHAR和VARCHAR值一起使用。当列被赋予该属性时，它们将以区分大小写的方式排序(根据它们的 ASCII 机器值)。这与省略BINARY属性时不区分大小写的排序形成对比。下面是一个BINARY列赋值的例子:

hostname CHAR(25) BINARY NOT 

NULL

系统默认值

默认属性确保当没有其他值可用时，将分配某个常数值。这个值必须是一个常量，因为 MySQL 不允许插入函数值或表达式值。此外，该属性不能与BLOB或TEXT字段一起使用。如果NULL属性已经分配给这个字段，如果没有指定默认值，默认值将为空。否则(具体来说，如果NOT NULL是伴随属性)，默认值将取决于字段数据类型。

下面是一个DEFAULT属性分配的例子:

subscribed ENUM('No','Yes') NOT NULL 

DEFAULT 'No'

指数

如果所有其他因素都相同，使用索引通常是加快数据库查询速度的最重要的一步。索引一个列会为该列创建一个排序的键数组，每个键都指向其对应的表行。随后在这个有序的键数组中搜索输入条件，与搜索整个无索引的表相比，性能会有很大的提高，因为 MySQL 已经拥有了这个有序的数组。以下示例演示了如何对用于存储雇员姓氏的列进行索引:

CREATE TABLE employees (
   id VARCHAR(9) NOT NULL,
   firstname VARCHAR(15) NOT NULL,
   lastname VARCHAR(25) NOT NULL,
   email VARCHAR(45) NOT NULL,
   phone VARCHAR(10) NOT NULL,
   INDEX lastname (lastname),
   PRIMARY KEY(id));

或者，可以在使用 MySQL 的CREATE INDEX命令创建表后添加索引:

CREATE INDEX lastname ON employees (lastname(7));

这一节与前一节略有不同，这次只索引名字的前七个字符，因为可能不需要更多的字母来区分名字。使用较小的索引时，Select 性能通常会更好，因此只要可行，就应该尽量使用较小的索引。插入性能会受到索引的影响，因为服务器必须插入数据并为新行创建所有索引条目。在批量插入的情况下，通常最好删除索引，插入数据，然后在表上重新创建索引。

国家的

NA可选属性仅与 CHAR 和 VARCHAR 数据类型结合使用。当指定时，它确保该列使用默认字符集，MySQL 默认已经这样做了。简而言之，这个属性是作为数据库兼容性的辅助工具提供的。

不为空

将列定义为NOT NULL将禁止任何向列中插入NULL值的尝试。总是建议在相关的地方使用NOT NULL属性，因为它至少会导致基线验证，即所有必需的值都已传递给查询。下面是一个NOT NULL列分配的例子:

zipcode VARCHAR(10) NOT 

NULL

空

NULL属性表示允许一列没有值。请记住，NULL是一个数学术语，表示“虚无”，而不是空字符串或零。当一个列被赋予NULL属性时，无论其他行字段是否已经被填充，该字段都有可能保持为空。

默认情况下，NULL属性被分配给一个字段。通常，您会希望避免此默认值，确保表中不接受空值。这是通过上面介绍的NULL的对立面NOT NULL来实现的。

主关键字

PRIMARY KEY属性用于保证给定行的唯一性。指定为主键的列中的值在该列中不可重复或为空。将AUTO_INCREMENT属性分配给被指定为主键的列是很常见的，因为该列不一定与行数据有任何关系，只是充当它的唯一标识符。但是，还有另外两种方法可以确保记录的唯一性:

• 单字段主键:当数据库中的每一行都有预先存在的、不可修改的唯一标识符时，通常使用单字段主键，比如零件号或社会保险号。请注意，该键一旦设置，就不应更改。主键不应包含除标识表中特定行之外的任何信息。

• 多字段主键:当不能保证一个记录中任何单个字段的唯一性时，多字段主键会很有用。因此，多个字段被连接起来以确保唯一性。国家和邮政编码就是一个例子。同一个邮政编码可能存在于多个国家，因此有必要使用国家和邮政编码的组合作为主键。当出现这种情况时，简单地指定一个AUTO_INCREMENT整数作为主键通常是个好主意；这减轻了每次插入都生成唯一标识符的需要。

以下三个示例分别演示了自动递增、单字段和多字段主键字段的创建。

创建自动递增的主键:

CREATE TABLE employees (
   id SMALLINT NOT NULL AUTO_INCREMENT,
   firstname VARCHAR(15) NOT NULL,
   lastname VARCHAR(25) NOT NULL,
   email VARCHAR(55) NOT NULL,
   PRIMARY KEY(id));

创建单字段主键:

CREATE TABLE citizens (
   id VARCHAR(9) NOT NULL,
   firstname VARCHAR(15) NOT NULL,
   lastname VARCHAR(25) NOT NULL,
   zipcode VARCHAR(9) NOT NULL,
   PRIMARY KEY(id));

创建多字段主键:

CREATE TABLE friends (
   firstname VARCHAR(15) NOT NULL,
   lastname VARCHAR(25) NOT NULL,
   nickname varchar(15) NOT NULL,
   PRIMARY KEY(lastname, nickname));

独一无二的

分配了UNIQUE属性的列将确保所有值都拥有不同的值，除了NULL值是可重复的。您通常将一个列指定为UNIQUE，以确保该列中的所有字段都是不同的——例如，防止同一个电子邮件地址被多次插入到新闻稿订户表中，同时承认该字段可能为空(NULL)。指定为UNIQUE的列的示例如下:

email VARCHAR(55) 

UNIQUE

零填充

ZEROFILL属性可用于任何数值类型，并将导致所有剩余的字段空间替换为零。例如，无符号INT的默认宽度是 10；因此，填零的INT值 4 将表示为 0000000004。下面是一个ZEROFILL属性分配的例子:

odometer MEDIUMINT UNSIGNED ZEROFILL NOT NULL

根据这个定义，值 35，678 将作为 0035678 返回。

使用数据库和表

学习如何管理和导航 MySQL 数据库和表是您想要掌握的首要任务之一。本节重点介绍几项关键任务。

使用数据库

本节演示了如何查看、创建、选择和删除 MySQL 数据库。

查看数据库

检索位于服务器上的数据库列表通常很有用。为此，执行SHOW DATABASES命令:

mysql>SHOW DATABASES;
+--------------------------------+
| Database                       |
+--------------------------------+
| information_schema             |
| book                           |
| corporate                      |
| mysql                          |
| test                           |
| wikidb                         |
+--------------------------------+
6 rows in set (0.57 sec)

请记住，用户权限会影响您查看给定服务器上所有可用数据库的能力。有关此事的更多信息，请参见第二十六章。

注意，使用SHOW DATABASES命令是 MySQL 版本 5.0.0 之前的标准方法。尽管该命令在 5.0.0 和更高版本中仍然可用，但请考虑使用通过INFORMATION_SCHEMA提供给您的命令。有关这个新特性的更多信息，请参见后面的“信息模式”一节。

创建数据库

创建数据库有两种常见的方法。也许最简单的方法是在mysql客户端中使用CREATE DATABASE命令创建它:

mysql>CREATE DATABASE company;
Query OK, 1 row affected (0.00 sec)

您也可以通过mysqladmin客户端创建一个数据库:

%>mysqladmin -u root -p create company
Enter password:
%>

数据库创建失败的常见问题包括权限不足或不正确，或者试图创建已经存在的数据库。

使用数据库

一旦创建了数据库，您可以通过使用USE命令将它指定为默认的工作数据库:

mysql>USE company;
Database changed

或者，您可以在通过mysql客户端登录时，通过在命令行上传递其名称来直接切换到该数据库，如下所示:

%>mysql -u root -p 

company

删除数据库

删除数据库的方式与创建数据库的方式非常相似。您可以使用DROP命令从mysql客户端中删除它，如下所示:

mysql>DROP DATABASE company;
Query OK, 1 row affected (0.00 sec)

或者，您可以从mysqladmin客户端删除它。这样做的好处是在删除之前会提示您:

%>mysqladmin -u root -p drop company
Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'company' database [y/N] y
Database "

company" 

dropped
%>

使用表格

在本节中，您将学习如何创建、列出、查看、删除和修改 MySQL 数据库表。

创建表格

使用CREATE TABLE语句创建一个表。虽然有大量的选项和条款专门针对这一声明，但在一个非正式的介绍中讨论它们似乎有点不切实际。相反，本节涵盖了本声明的各种功能，因为它们在以后的章节中会变得相关。尽管如此，这里将演示一般用法。例如，下面创建了本章开始时讨论的employees表:

CREATE TABLE employees (
   id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   firstname VARCHAR(25) NOT NULL,
   lastname VARCHAR(25) NOT NULL,
   email VARCHAR(45) NOT NULL,
   phone VARCHAR(10) NOT NULL,
   PRIMARY KEY(id));

请记住，一个表必须至少包含一列。此外，在创建表结构之后，您可以随时返回并修改它。在本节的后面，您将了解如何通过ALTER TABLE语句来实现这一点。

无论当前是否使用目标数据库，您都可以创建一个表。只需在表名前面加上目标数据库名，如下所示:

database_name.table_name

有条件地创建表

默认情况下，如果您试图创建一个已经存在的表，MySQL 会生成一个错误。为了避免这个错误，CREATE TABLE语句提供了一个子句，如果您想在目标表已经存在的情况下简单地中止创建表的尝试，可以包含这个子句。例如，假设您想要分发一个依赖 MySQL 数据库存储数据的应用。因为有些用户会下载最新版本作为升级的必然选择，而有些用户会第一次下载，所以您的安装脚本需要一种简单的方法来创建新用户的表，同时不会在升级过程中导致错误的过度显示。这是通过IF NOT EXISTS子句完成的。因此，如果您想创建一个不存在的employees表，请执行以下操作:

CREATE TABLE IF NOT EXISTS employees (
   id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
   firstname VARCHAR(25) NOT NULL,
   lastname VARCHAR(25) NOT NULL,
   email VARCHAR(45) NOT NULL,
   phone VARCHAR(10) NOT NULL,
   PRIMARY KEY(id));

这个动作的一个奇怪之处是输出没有指定表是否被创建。在返回到命令提示符之前，这两种变体都显示“Query OK”消息。

复制表格

基于现有的表创建一个新表是一件很简单的事情。下面的查询产生了一个employees表的精确副本，将其命名为employees2:

CREATE TABLE employees2 SELECT * FROM employees;

一个相同的表employees2将被添加到数据库中。

有时，您需要仅基于在预先存在的表中找到的几列来创建表。您可以通过简单地在CREATE SELECT语句中指定列来做到这一点:

CREATE TABLE employees3 SELECT firstname, lastname FROM 

employees;

创建临时表

有时，创建生存期仅与当前会话一样长的表很有用。例如，您可能需要对一个特别大的表的子集执行几个查询。您可以为该子集创建一个临时表，然后对其运行查询，而不是对整个表重复运行这些查询。这是通过结合使用TEMPORARY关键字和CREATE TABLE语句来实现的:

CREATE TEMPORARY TABLE emp_temp SELECT firstname,lastname FROM employees;

创建临时表就像创建任何其他表一样，只是它们存储在操作系统指定的临时目录中，在 Linux 上通常是/tmp或/usr/tmp。您可以通过设置 MySQL 的TMPDIR环境变量来覆盖这个默认值。

注意

从 MySQL 4.0.2 开始，创建临时表需要拥有CREATE TEMPORARY TABLE特权。参见第二十六章了解更多关于 MySQL 特权系统的细节。

查看数据库的可用表

您可以使用SHOW TABLES语句查看数据库可用的表列表:

mysql>SHOW TABLES;
+-------------------------------+
| Tables_in_company             |
+-------------------------------+
| employees                     |
+-------------------------------+
1 row in set (0.00 sec)

请注意，这是 MySQL 版本 5.0.0 之前的标准方法。尽管该命令在 5.0.0 及更高版本中仍然可用，但请考虑使用通过INFORMATION_SCHEMA提供给您的命令。有关这个新特性的更多信息，请参见后面的“信息模式”一节。

查看表格结构

您可以使用DESCRIBE语句查看表格结构:

mysql>DESCRIBE employees;
+-----------+---------------------+------+-----+---------+---------------+
| Field     | Type                | Null | Key | Default | Extra         |
+-----------+---------------------+------+-----+---------+---------------+
| id        | tinyint(3) unsigned |      | PRI | NULL    | auto_increment|
| firstname | varchar(25)         |      |     |         |               |
| lastname  | varchar(25)         |      |     |         |               |
| email     | varchar(45)         |      |     |         |               |
| phone     | varchar(10)         |      |     |         |               |
+-----------+---------------------+------+-----+---------+---------------+

或者，您可以像这样使用SHOW命令来产生相同的结果:

mysql>SHOW columns IN employees;

如果您想对如何解析模式进行更多的控制，可以考虑使用 INFORMATION_SCHEMA 提供的命令，这将在下一节“INFORMATION_SCHEMA”中介绍

删除表格

删除一个表是通过DROP TABLE语句完成的。其语法如下:

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name,...]

例如，您可以删除您的employees表，如下所示:

DROP TABLE employees;

您也可以像这样同时删除employees2和employees3表格:

DROP 

TABLE 

employees2, employees3;

改变表格结构

您会发现自己经常修改和改进表结构，尤其是在开发的早期阶段。但是，您不必在每次想要进行更改时都经历删除和重新创建表的麻烦。相反，您可以用ALTER语句改变表的结构。使用该语句，您可以根据需要删除、修改和添加列。像CREATE TABLE一样，ALTER TABLE语句提供了大量的子句、关键字和选项。这留给你在 MySQL 手册中查找血淋淋的细节。本节提供了几个示例，旨在帮助您快速入门，从添加列开始。假设您想用employees表跟踪每个雇员的生日:

ALTER TABLE employees ADD COLUMN birthdate DATE;

新列被放置在表格的最后一个位置。但是，您也可以通过使用适当的关键字来控制新列的定位，包括FIRST、AFTER和LAST。例如，您可以将birthdate列直接放在lastname列之后，如下所示:

ALTER TABLE employees ADD COLUMN birthdate DATE AFTER lastname;

呜呜，你忘了NOT NULL条款！您可以修改新列:

ALTER TABLE employees CHANGE birthdate birthdate DATE NOT NULL;

最后，在这一切之后，您决定没有必要跟踪员工的生日。继续删除该列:

ALTER TABLE employees DROP 

birthdate;

信息模式

在本章的前面，您已经了解到SHOW命令用于了解服务器中的数据库、数据库中的表以及组成表的列。事实上，SHOW用于了解服务器的配置，包括用户权限、支持的表引擎、执行的进程等等。问题是SHOW不是一个标准的数据库特性；这完全是 MySQL 固有的东西。此外，它不是特别强大。例如，不可能使用命令来了解表的引擎类型。比方说，也不能轻易地找出一组给定表中的哪些列是类型VARCHAR。版本 5.0.2 中INFORMATION_SCHEMA的引入解决了这样的问题。

在 SQL 标准的支持下，INFORMATION_SCHEMA提供了一个使用典型的选择查询来了解更多关于数据库和各种服务器设置的解决方案。由 28 个表组成，它可以了解您安装的几乎每一个方面。下表列出了表名和简要描述:

• CHARACTER_SETS:存储可用字符集的信息。

• COLLATIONS:存储关于字符集归类的信息。

• COLLATION_CHARACTER_SET_APPLICABILITY:是INFORMATION_SCHEMA.COLLATIONS表的子集，它将字符集与各自的归类进行匹配。

• COLUMNS:存储关于表格列的信息，例如列名、数据类型以及是否可以为空。

• COLUMN_PRIVILEGES:存储列权限信息。请记住，该信息实际上是从mysql.columns_priv表中检索的；但是，从该表中检索它提供了在查询数据库属性时获得额外一致性的机会。更多信息请参见第二十九章。

• ENGINES:存储可用存储引擎的信息。

• EVENTS:存储预定事件的信息。预定事件超出了本书的范围；更多信息请参考 MySQL 文档。

• FILES:存储 NDB 磁盘数据表信息。NDB 是一个存储引擎，超出了本书的范围；更多信息请参考 MySQL 文档。

• GLOBAL_STATUS:存储服务器状态变量的信息。

• GLOBAL_VARIABLES:存储有关服务器设置的信息。

• KEY_COLUMN_USAGE:存储关于键列约束的信息。

• PARTITIONS:存储关于表分区的信息。

• 存储关于插件的信息，这是 MySQL 5.1 的新特性，不在本书讨论范围之内。更多信息请参考 MySQL 文档。

• PROCESSLIST:存储当前正在运行的线程的信息。

• PROFILING:存储查询概要信息。您也可以通过执行SHOW PROFILE和SHOW PROFILES命令找到这些信息。

• REFERENTIAL_CONSTRAINTS:存储关于外键的信息。

• ROUTINES:存储关于存储过程和函数的信息。见第三十二章了解更多关于这个话题的信息。

• SCHEMATA:存储位于服务器上的数据库信息，如数据库名称和默认字符集。

• SCHEMA_PRIVILEGES:存储数据库权限信息。请记住，该信息实际上是从mysql.db表中检索的；但是，从该表中检索它提供了在查询数据库属性时获得额外一致性的机会。关于这个主题的更多信息，请参见第二十九章。

• SESSION_STATUS:存储当前会话的信息。

• SESSION_VARIABLES:存储当前会话的配置信息。

• STATISTICS:存储关于每个表索引的信息，比如列名、是否可以为空，以及每行是否必须是唯一的。

• TABLES:存储每个表的信息，比如名称、引擎、创建时间和平均行长度。

• TABLE_CONSTRAINTS:存储表约束的信息，如是否包含UNIQUE和PRIMARY KEY列。

• TABLE_PRIVILEGES:存储表权限信息。请记住，该信息实际上是从mysql.tables_priv表中检索的；但是，从该表中检索它提供了在查询数据库属性时获得额外一致性的机会。更多信息请参见第二十九章。

• TRIGGERS:存储关于每个触发器的信息，例如它是否根据插入、删除或修改而触发。注意，直到版本 5.0.10，这个表才被添加到INFORMATION_SCHEMA中。详见第三十三章。

• USER_PRIVILEGES:存储全局权限信息。请记住，该信息实际上是从mysql.user表中检索的；但是，从该表中检索它提供了在查询数据库属性时获得额外一致性的机会。更多信息请参见第二十九章。

• VIEWS:存储关于每个视图的信息，比如它的定义和它是否可更新。更多信息见第三十四章。

要检索驻留在服务器上的数据库中找到的所有表名和相应引擎类型的列表，除了在mysql数据库中找到的那些，执行以下命令:

mysql>USE INFORMATION_SCHEMA;
mysql>SELECT table_name FROM tables WHERE table_schema != 'mysql';
+------------------------+--------+
| table_name             | engine |
+------------------------+--------+
| authentication_dynamic | MyISAM |
| authentication_static  | MyISAM |
| products               | InnoDB |
| selectallproducts      | NULL   |
| users                  | MEMORY |
+------------------------+--------+
5 rows in set (0.09 sec)

要选择公司数据库中数据类型为VARCHAR的表名和列名，执行以下命令:

mysql>select table_name, column_name from columns WHERE
    -> data_type="varchar" and table_schema="corporate";
+------------------------+-------------+
| table_name             | column_name |
+------------------------+-------------+
| authentication_dynamic | username    |
| authentication_dynamic | pswd        |
| products               | name        |
| selectallproducts      | name        |
| users                  | username    |
| users                  | pswd        |
+------------------------+-------------+
6 rows in set (0.02 sec)

从这些简短的例子中可以看出，使用SELECT查询来检索这些信息比使用SHOW要灵活得多。此外，SHOW司令部不太可能很快消失。因此，如果你只是在寻找一个快速的摘要，比如说，在服务器上找到的数据库，你肯定会通过继续使用SHOW来节省一些击键的时间。

摘要

在本章中，您了解了 MySQL 表设计中的许多要素。这一章以对 MySQL 存储引擎的调查开始了讨论，讨论了每种引擎的用途和优点。讨论之后介绍了 MySQL 支持的数据类型，提供了关于每种数据类型的名称、用途和范围的信息。然后研究了许多最常用的属性，这些属性用于进一步调整列的行为。这一章接着介绍了一个关于基本 MySQL 管理命令的简短教程，演示了如何列出、创建、删除、浏览和修改数据库和表。最后，向您介绍了 MySQL 5.0.2 和更新版本中的INFORMATION_SCHEMA特性。这一章还提到了名为 MariaDB 的数据库，以及该数据库与 MySQL 兼容的事实，因为它共享相同的根。

下一章深入探讨 MySQL 的另一个关键特性:安全性。您将了解 MySQL 强大的特权表。您还将了解如何保护 MySQL 服务器守护进程，以及如何使用 SSL 创建安全的 MySQL 连接。

二十六、保护 MySQL

这已经成为一种自然的反应:当你走出家门或汽车时，你会花一点时间锁上门，设置闹钟。您这样做是因为您知道忽略采取这些基本而有效的预防措施会大大增加您的财产被盗或损坏的可能性。具有讽刺意味的是，整个 IT 行业似乎采取了相反的方法。尽管知识产权盗窃和破坏在企业 IT 系统中普遍存在，但许多开发人员继续投入最少的时间和精力来创建安全的计算环境。尽管包括 MySQL 在内的许多软件产品都以最低的配置要求提供了强大的内置安全特性，但还是出现了这种情况。在这一章中，我将介绍 MySQL 高效的基于权限的访问模型，通过大量的例子展示给你的数据库增加一个看似不可逾越的安全层是多么容易。

注意

恶意攻击并不是数据损坏或破坏的唯一原因。太多的开发人员和管理员选择使用拥有远远超过所需权限的帐户。最终，执行了一个从一开始就不应该被允许的命令，导致了严重的损害。这一章告诉你如何避免这样的灾难。

本章非常详细地介绍了 MySQL 所谓的用户权限系统，向您展示了如何创建用户、管理权限和更改密码。此外，还介绍了 MySQL 的安全(SSL)连接特性。您还将学习如何限制用户资源消耗。完成本章后，您应该熟悉以下主题:

• 首次启动 MySQL 守护进程后应立即采取的步骤

• 如何保护mysqld守护进程

• MySQL 的访问权限系统

• GRANT和REVOKE功能

• 用户帐户管理

• 使用 SSL 创建安全的 MySQL 连接

请记住，保护 MySQL 只是保护系统所需的步骤之一。运行 MySQL 服务器和可能的 web 服务器的操作系统应该总是打补丁，并且所有端口都由防火墙保护，因此只有需要的端口暴露给外界(端口 22 上的 SSH 和端口 80 和 443 上的 http/https 应该在大多数基于 Linux 的托管环境中开放)。您还应该记住保护 web 应用的安全，并确保它在设计时考虑到了安全性，并防止跨端脚本和 SQL 注入。(参见第十九章)让我们从头开始:在用 MySQL 数据库服务器做其他事情之前，你应该做什么*。*

你应该先做什么

本节概述了在完成第二十三章中概述的安装和配置过程后，您应该立即执行的几项基本但非常重要的任务:

• 为操作系统和任何已安装的软件打补丁:如今，软件安全更新似乎每周都会发布，尽管它们很烦人，但你绝对有必要采取措施确保你的系统打满补丁。有了因特网上现成的明确说明和工具，即使是恶意的新手也能轻而易举地利用未打补丁的服务器。自动扫描设备增加了您的未打补丁的服务器被发现并受到危害的可能性。如果你考虑将应用托管在一个托管提供商处，那么一定要研究该提供商的安全记录，以确保及时应用补丁。大多数 Linux 发行版都提供了一种在更新可用时得到通知的方式。在 Red Hat 和 CentOS 系统上，这是通过安装 yum.cron 包来完成的。

• 禁用所有未使用的系统服务:在将服务器连接到网络之前，请务必禁用所有不必要的系统服务。例如，如果您不打算从服务器发送电子邮件，那么就没有理由让服务器的 SMTP 守护进程处于启用状态。

• 关闭防火墙:虽然关闭未使用的系统服务是减少攻击成功概率的一个很好的方法，但是通过关闭所有未使用的端口来增加第二层安全保护是没有坏处的。对于专用的数据库服务器，考虑关闭除指定的 SSH 端口 3306 (MySQL)和少数“实用程序”端口(如 123 (NTP))之外的所有端口。除了在专用防火墙设备或路由上进行此类调整之外，还可以考虑利用操作系统的防火墙。还可以考虑配置防火墙，禁止除本地网络上的计算机之外的任何地址访问端口 3306。如果有必要通过互联网连接管理服务器，建议使用私钥/公钥来访问 ssh 服务，而不是使用 usrid/密码。

• 审计服务器的用户帐户:特别是如果一个已存在的服务器被重新用于托管组织的数据库，确保所有非特权用户被禁用，或者更好的是，被删除。尽管您很快就会了解到，MySQL 用户和操作系统用户是完全不相关的，但是后者用户能够访问服务器环境这一事实就增加了对数据库服务器及其内容造成损害的可能性，无论是无意的还是其他方式。为了完全确保在此类审核过程中没有任何遗漏，请考虑重新格式化所有服务器驱动器并重新安装操作系统。

• 设置 MySQL root 用户密码:默认情况下，MySQL root(管理员)账户密码为空。因此，如果您还没有设置 root 用户的默认密码，那么您应该注意立即设置它！您可以使用SET PASSWORD命令来完成，如下所示:

  %> mysql -u root mysql
  %> UPDATE mysql.user SET Password = PASSWORD('secret');
  %> flush privileges;
  
  

• 当然要选择明显比secret复杂的密码。MySQL 会让你自掘坟墓，因为密码 123 和 abc 是完全可以接受的。考虑选择长度至少为八个字符的密码，并且由数字、字母和不同大小写的特殊字符混合组成。

• 建议在安装后立即使用mysql_secure_installation脚本。这不仅会设置 root 密码，还会执行其他有助于创建更安全环境的操作。

保护 mysqld 守护进程

在第二十四章中，你学习了如何启动 MySQL 服务器守护进程mysqld。当您启动mysqld守护进程时，您可以使用几个安全选项:

• --chroot:将服务器置于受限环境中，改变 MySQL 服务器识别的操作系统根目录。如果服务器通过 MySQL 数据库遭到破坏，这将极大地限制意外的后果。您必须在新的根结构中安装额外的库，以便像 MySQL 这样的应用运行。

• --skip-networking:当连接到 MySQL 时，禁止使用 TCP/IP 套接字，这意味着无论提供什么凭证，都不接受远程连接。如果您的应用和数据库驻留在同一台服务器上，您应该考虑启用此选项。

• --skip-name-resolve:连接 MySQL 数据库时禁止使用主机名，只允许使用 IP 地址或本地主机。这将强制连接到一个特定的 IP 地址，而不依赖于外部 DNS 服务器，因为外部 DNS 服务器可能会将主机名解析为不同的 IP 地址。

• --skip-show-database:防止任何没有show databases权限的用户使用该命令查看服务器上托管的所有数据库的列表。您可以通过show databases权限为每个用户启用该功能。(有关user工作台的更多信息，请参见下一节。)当然，如果用户拥有某个特定于数据库的特权，那么仅仅拥有该特权就会导致相关数据库被列出来，以响应show databases命令的执行。

• --safe-user-create:防止任何用户通过grant命令创建新用户，如果他们没有mysql.user表的insert权限。

MySQL 访问权限系统

保护您的数据免受不必要的审查、修改或删除——无论是意外的还是其他的——应该始终是一个首要问题。然而，平衡安全性和便利性通常是一项艰巨的挑战。当您考虑任何给定环境中可能存在的大量访问场景时，这种平衡的微妙性就变得显而易见了。例如，如果用户需要修改权限，但不需要插入权限，该怎么办？如何对可能需要从多个不同 IP 地址访问数据库的用户进行身份验证？如果您希望向用户提供对某些表列的读取权限，而限制对其余列的访问权限，该怎么办？幸运的是，MySQL 开发人员已经考虑到了这些情况，将全功能的认证和授权功能集成到了服务器中。这通常被称为 MySQL 的特权系统，它依赖于一个名为mysql (this is the name even if you're using the MariaDB version)的特殊数据库，该数据库存在于所有 MySQL 服务器上。在这一节中，我将解释特权系统是如何工作的，提到这个数据库中的各种表在实现这个强大的安全特性中扮演的角色。在这个概述之后，我将更深入地研究这些表，正式介绍它们的角色、内容和结构。

特权系统如何工作

MySQL 的特权系统基于两个基本概念:

• 认证 : 用户甚至可以连接到服务器吗？

• 授权 : 被认证的用户是否拥有足够的特权来执行所需的查询？

因为没有成功的身份验证就不能进行授权，所以您可以将这个过程看作分两个阶段进行。

访问控制的两个阶段

一般的权限控制过程发生在两个不同的阶段:连接认证和请求验证。这些阶段总共分为五个不同的步骤。

1. MySQL 使用user表来决定应该接受还是拒绝传入的连接。这是通过将指定的主机和用户与包含在user表中的一行进行匹配来实现的。MySQL 还确定用户是否需要安全的连接，以及是否超过了该帐户每小时允许的最大连接数。步骤 1 的执行完成了权限控制过程的认证阶段。

2. 步骤 2 启动权限控制流程的授权阶段。如果连接被接受，MySQL 会验证是否超过了该帐户每小时允许的最大查询或更新次数。接下来，检查在user表中授予的相应特权。如果这些特权中的任何一个被启用(设置为y，那么用户就能够以该特权所授予的能力对驻留在该服务器上的任何数据库进行操作。正确配置的 MySQL 服务器可能会禁用所有这些权限，这将导致步骤 3 的发生。

3. 检查db表是为了确定用户是否有与任何特定数据库交互的特权。该表中启用的任何特权都适用于那些授权数据库中的所有表。如果没有启用任何权限，但是找到了匹配的用户和主机值，则该过程继续到步骤 5。如果找到匹配的用户，但没有相应的主机值，则该过程继续到步骤 4。

4. 如果发现db表中的一行有匹配的用户，但主机值为空，那么就检查host表。如果在这个表中找到一个匹配的主机值，用户将被分配到那个数据库的特权，如在host表中所示，而不是在db表中。这是为了允许特定主机对特定数据库的访问。

5. 最后，如果用户试图执行在user、db或host表中没有授权的命令，则tables_priv, columns_priv and proc_priv表将被检查，以确定用户是否能够在相关的表、列或过程上执行所需的命令。此外，还可以使用代理用户授予访问权限，使该用户与系统中的其他用户拥有相同的访问权限。

您可能已经从流程分解中了解到，系统检查权限的方式是从非常宽泛的权限开始，到非常具体的权限结束。让我们考虑一个具体的例子。

跟踪真实世界的连接请求

假设用户jason从由192.168.1.2标识的客户端主机连接并使用密码secret想要在category table中插入一个新行，该行在sakila数据库中找到。MySQL 首先确定jason@192.168.1.2是否被授权连接到数据库，如果是，则确定是否允许他执行insert请求。让我们考虑一下在执行这两种验证时，在幕后发生了什么。

1. 用户jason@192.168.1.2需要安全连接吗？如果是，并且用户jason@192.168.1.2试图在没有所需安全证书的情况下连接，则拒绝该请求并结束认证过程。如果没有，请继续执行步骤 2。

2. 确定用户jason@192.168.1.2是否已超过每小时连接的最大允许数量，如果是，则拒绝认证过程。MySQL 接下来会判断是否超过了最大同时连接数。如果两个条件都被认为是假的，则继续步骤 3。否则，拒绝请求。

3. 用户jason@192.168.1.2是否拥有连接数据库服务器所需的权限？如果是，请继续执行步骤 4。如果没有，则拒绝访问。该步骤结束了特权控制机制的认证组件。

4. 用户jason@192.168.1.2是否超过了允许更新或查询的最大数量？如果没有，请继续执行步骤 5。否则，拒绝请求。

5. 用户jason@192.168.1.2是否拥有全局 insert权限？如果是，接受并执行插入请求。如果没有，请继续执行步骤 6。

6. 用户jason@192.168.1.2是否拥有company数据库的insert权限？如果是，接受并执行插入请求。如果没有，请继续执行步骤 7。

7. 用户jason@192.168.1.2是否拥有插入请求中指定的widgets表列的insert特权？如果是，接受并执行插入请求。如果否，则拒绝该请求并结束控制过程。

到目前为止，您应该开始理解 MySQL 的访问控制机制的一般性。然而，在你熟悉这个过程的技术基础之前，这幅图是不完整的，所以继续读下去

访问信息存储在哪里？

MySQL 的权限验证信息存储在默认安装的mysql数据库中。具体来说，该数据库中的七个表在身份验证和特权验证过程中起着重要的作用:

• user:决定哪些用户可以从哪个主机登录数据库服务器。

• db:决定哪些用户可以访问哪些数据库。

• host:对db表的扩展，提供了额外的主机名，用户可以通过这些主机名连接到数据库服务器。

• tables_priv:决定哪些用户可以访问特定数据库的特定表。

• columns_priv:决定哪些用户可以访问特定表格的特定列。

• procs_priv:控制存储过程的使用。

• proxies _ priv:从 MySQL 5.5.7 开始可用，这个表管理代理用户的特权。这个话题超出了本书的范围，不会再进一步讨论。

本节深入研究了与每个权限表的目的和结构相关的细节。

用户表

从这个意义上来说,user表是唯一一个在特权请求程序的两个阶段都起作用的特权表。在认证阶段，user表单独负责授权用户访问 MySQL 服务器。它还确定用户是否超过了每小时允许的最大连接数(如果已配置)，以及用户是否超过了最大同时连接数(如果已配置)。有关在每个用户的基础上控制资源使用的更多信息，请参见“限制用户资源”一节。在这个阶段，user表还决定是否需要基于 SSL 的授权；如果是，则user表检查必要的凭证。有关该特性的更多信息，请参见“保护 MySQL 连接”一节。

在请求授权阶段，user表确定是否有任何被授权访问服务器的用户被分配了使用 MySQL 服务器的全局特权(这在大多数情况下是不应该发生的)。也就是说，该表中启用的任何权限都允许用户以某种身份使用位于该 MySQL 服务器上的所有数据库*。在这个阶段，use r 表还确定用户是否超过了每小时允许的最大查询和更新次数。*

user表拥有另一个定义性特征:它是唯一一个存储与 MySQL 服务器管理相关的特权的表。例如，该表负责确定允许哪些用户执行与服务器常规功能相关的命令，如关闭服务器、重新加载用户权限、查看甚至终止现有的客户端进程。因此，user表在 MySQL 操作的许多方面都起着重要的作用。

由于其广泛的职责，user是权限表中最大的，总共包含 42 个字段或列。在这一节中，我将介绍各种权限配置情况下最常用的字段。

圣体

Host列指定了主机名，该主机名决定了用户可以连接的主机地址。地址可以存储为主机名、IP 地址或通配符。通配符可以由%或_字符组成。此外，网络掩码可用于表示 IP 子网。以下是几个示例条目:

• www.example.com

• 192.168.1.2

• %

• %. example.com

• 192.168.1.0/255.255.255.0

• localhost

用户

User列指定能够连接到数据库服务器的用户的区分大小写的名称。尽管不允许使用通配符，但空值是允许的。如果条目为空，则允许来自相应主机条目的任何用户登录到数据库服务器。条目示例如下:

• jason

• Jason_Gilmore

• secretary5

密码

Password列存储连接用户提供的加密密码。尽管不允许使用通配符，但空白密码是允许的。因此，请确保所有用户帐户都附有相应的密码，以减轻潜在的安全问题。密码以单向哈希格式存储，这意味着它们不能转换回纯文本格式。

用户识别

MySQL 不仅通过提供的用户名来标识用户，还通过提供的用户名和原始主机名的组合来标识用户:例如，jason@localhost与jason@192.168.1.12完全不同。此外，请记住，MySQL 将始终应用与所提供的user@host组合相匹配的最特定的权限集。虽然这似乎是显而易见的，但有时会发生不可预见的后果。例如，经常会有多行匹配请求的用户/主机标识；即使满足所提供的user@host组合的通配符条目出现在与身份完全匹配的后续条目之前，也将使用与该完全匹配相对应的特权，而不是通配符匹配。因此，要始终注意确保确实为每个用户提供了预期的权限。在本章的后面，您将看到如何查看每个用户的权限。

权限列

接下来列出的 29 列包括用户权限列。请记住，当在用户表的上下文中讨论时，这些代表用户的全局权限。

• Select_priv:决定用户是否可以选择数据。

• Insert_priv:决定用户是否可以插入数据。

• Update_priv:决定用户是否可以修改已有数据。

• Delete_priv:决定用户是否可以删除已有数据。

• Create_priv:决定用户是否可以创建新的数据库和表格。

• Drop_priv:决定用户是否可以删除已有的数据库和表格。

• Reload_priv:决定用户是否可以执行专门针对 MySQL 使用的各种内部缓存的刷新和重载的各种命令，包括日志、权限、主机、查询、表。

• Shutdown_priv:决定用户是否可以关闭 MySQL 服务器。您应该非常小心，不要将此权限提供给除 root 帐户之外的任何人。

• Process_priv:决定用户是否可以通过show processlist命令查看其他用户的进程。

• File_priv:决定用户是否可以执行select into outfile和load data infile命令。

• Grant_priv:决定用户是否可以将自己已经拥有的权限授予其他用户。例如，如果用户可以插入、选择和删除位于foo数据库中的信息，并且被授予了grant权限，那么该用户可以将这些权限中的任何或所有权限授予位于系统中的任何其他用户。

• References_priv:目前只是某个未来函数的占位符；这在这个时候没有用。

• Index_priv:决定用户是否可以创建和删除表格索引。

• er_priv:决定用户是否可以重命名和改变表结构。

• Show_db_priv:确定用户是否可以查看驻留在服务器上的所有数据库的名称，包括用户拥有足够访问权限的数据库。除非有特别令人信服的理由，否则请考虑对所有用户禁用此功能。

• Super_priv:决定用户是否可以执行某些强大的管理功能，例如通过kill命令删除用户进程，使用set global改变全局 MySQL 变量，以及执行与复制和日志记录相关的各种命令。

• Create_tmp_table_priv:决定用户是否可以创建临时表。

• Lock_tables_priv:决定用户是否可以使用lock tables命令阻止表格访问/修改。

• Execute_priv:决定用户是否可以执行存储过程。

• Repl_slave_priv:确定用户是否可以读取用于维护复制数据库环境的二进制日志文件。

• Repl_client_priv:决定用户是否可以确定任何复制从设备和主设备的位置。

• Create_view_priv:决定用户是否可以创建视图。

• Show_view_priv:决定用户是否可以看到一个视图或了解更多关于它如何执行的信息。

• Create_routine_priv:决定用户是否可以创建存储过程和函数。

• Alter_routine_priv:决定用户是否可以改变或删除存储过程和函数。

• Create_user_priv:决定用户是否可以执行create user语句，该语句用于创建新的 MySQL 账户。

• Event_priv:决定用户是否可以创建、修改、删除事件。

• Trigger_priv:决定用户是否可以创建和删除触发器。

• Create_tablespace_priv:确定用户是否可以创建新表。

数据库表

db表用于在每个数据库的基础上为用户分配权限。检查请求用户是否不拥有他试图执行的任务的全局特权。如果匹配的用户/主机/数据库三元组位于db表中，并且请求的任务已经被授予该行，那么请求被执行。如果不满足User / Host / Db任务匹配，则发生两个事件之一:

• 如果找到了一个User / Db匹配，但是主机是空白的，那么 MySQL 会向host表寻求帮助。下一节将介绍host表的用途和结构。

• 如果找到了User / Host / Db三元组，但是特权被禁用，MySQL 接下来会向tables_priv表寻求帮助。在后面的章节中将介绍tables_priv表的用途和结构。

由%和_字符表示的通配符可以在Host和Db列中使用，但不能在User列中使用。像user表一样，对行进行排序，以便最具体的匹配优先于不太具体的匹配。一定要切换到 MySQL 数据库，花点时间回顾一下有什么可用的。

主机表

只有当db牌桌的Host字段留空时，host牌桌才会起作用。如果一个特定的用户需要从不同的主机访问，您可以将db表的Host字段留空。不是为该用户复制和维护几个User / Host / Db实例，而是只添加一个实例(带有空白的Host字段)，相应的主机地址存储在host表的Host字段中。

由%和_字符表示的通配符可以在Host和Db列中使用，但不能在User列中使用。像user表一样，对行进行排序，以便最具体的匹配优先于不太具体的匹配。与到目前为止介绍的表一样，许多列的用途通过阅读它们的名称就很明显了，所以一定要切换到 MySQL 数据库，花点时间回顾一下有哪些可用的内容。

tables_priv 表

tables_priv表旨在存储特定于表的用户特权。只有当user、db和host表不满足用户的任务请求时，它才会起作用。为了更好地说明它的用法，请考虑一个例子。假设来自主机192.168.1.12的用户jason想要对位于数据库sakila中的表category执行一个update。一旦发起请求，MySQL 就开始检查user表，查看jason@192.168.1.12是否拥有全局update特权。如果不是这种情况，接下来检查db和host表，以获得特定于数据库的修改权限。如果这些表不满足请求，MySQL 就会查看tables_priv表来验证用户jason@192.168.1.12是否拥有在sakila数据库中找到的表category的更新特权。与到目前为止介绍的表一样，许多列的用途通过阅读它们的名称就很明显了，所以一定要切换到 MySQL 数据库，花点时间回顾一下有哪些可用的内容。

在tables_priv表中找到的所有列都应该是熟悉的，除了以下几列:

• Table_name:决定将在tables_priv表中设置的特定于表的权限应用到哪个表。

• Grantor:指定授予用户权限的用户的用户名。

• Timestamp:指定授予用户权限的确切日期和时间。

• Table_priv:决定用户可以使用哪些表范围的权限。可以以此身份申请以下权限:select、insert、update、delete、create、drop、grant、references、index、alter、create view、show view、trigger。

• Column_priv:存储分配给该用户的由Table_name列引用的表的任何列级特权的名称。这样做的目的是没有记录的，尽管有人会怀疑这样做是为了提高总体性能。

columns_priv 表

columns_priv表负责设置特定于列的特权。只有当user、db / host和tables_priv表不能确定请求用户是否有足够的权限来执行所请求的任务时，它才会起作用。与到目前为止介绍的表一样，这个表的许多列的用途通过阅读它们的名称就很明显了，所以一定要切换到 MySQL 数据库，花点时间回顾一下有哪些可用的内容。该表中的所有其他列都应该是熟悉的，除了Column_name，它指定了受GRANT命令影响的表列的名称。

procs_priv 表

procs_priv表管理存储过程和函数的使用。列Routine_name标识分配给用户的例程的名称，Routine_type标识例程的类型(函数或过程)，Grantor标识授予使用该例程的权限的用户，Proc_priv定义被授权者可以对该例程做什么(执行、更改或授权)。

用户和权限管理

位于mysql数据库中的表与任何其他关系表没有什么不同，因为它们的结构和数据可以使用典型的 SQL 命令来修改。然而，在这些表中管理的数据是使用两个方便的命令来管理的:命令grant和revoke。使用这些命令，可以创建和禁用用户，并且可以使用更加直观和简单的语法授予和撤销他们的访问权限。它们精确的语法消除了潜在的可怕错误，否则这些错误可能会由于畸形的 SQL 查询而引入(例如，忘记在update查询中包含where子句)。

由于使用这些命令来创建和有效地删除用户的能力可能看起来有点不直观，因为这些命令的名称意味着授予现有用户权限和撤销现有用户权限的想法，所以在 5.0.2 版本中，MySQL 的管理库中增加了两个新命令:create user和drop user。这个版本还增加了第三个命令，rename user (，用于重命名现有用户。

创建用户

create user命令用于创建新的用户账户。在创建时没有分配权限，这意味着您接下来需要使用grant命令来分配权限。该命令如下所示:

CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
 [, user [IDENTIFIED BY [PASSWORD] 'password']] ...

下面是一个例子:

mysql> create user 'jason'@'localhost' identified by 'secret';
Query OK, 0 rows affected (0.47 sec)

正如您在命令原型中看到的，还可以同时创建多个用户。这是通过提供带有相关密码的逗号分隔的用户列表来实现的。

删除用户

如果不再需要某个帐户，您应该考虑删除它，以确保它不能用于潜在的非法活动。这可以通过drop user命令轻松完成，该命令从特权表中删除用户的所有痕迹。命令语法如下所示:

DROP USER user [, user]...

下面是一个例子:

mysql> drop user 'jason'@'localhost';
Query OK, 0 rows affected (0.03 sec)

正如您在命令原型中看到的，还可以同时删除多个用户。

重命名用户

有时，您可能希望重命名现有用户。这很容易用RENAME USER命令来完成。其语法如下:

RENAME USER old_user TO new_user,
 [old_user TO new_user]...

下面是一个例子:

mysql> rename user 'jason'@'localhost' to 'jasongilmore'@'localhost';
Query OK, 0 rows affected (0.02 sec)

正如命令原型所示，还可以同时重命名多个用户。

授权和撤销命令

grant和revoke命令用于管理访问权限。这些命令提供了对谁可以处理服务器及其内容的几乎所有方面的大量细粒度控制，从谁可以关闭服务器到谁可以修改特定表列中的信息。表 26-1 列出了使用这些命令可以授予或撤销的所有可能的特权。

小费

虽然不赞成使用标准 SQL 语法修改mysql表，但是您可以这样做。请记住，对这些表所做的任何更改都必须使用flush-privileges命令。因为这是一种过时的管理用户权限的方法，所以没有提供关于这个问题的更多细节。更多信息请参见 MySQL 文档。

表 26-1

由 Grant 和 Revoke 管理的常用权限

|

特权

|

描述

|
| — | — |
| ALL PRIVILEGES | 影响除with grant option以外的所有权限 |
| ALTER | 影响alter table命令的使用 |
| ALTER ROUTINE | 影响改变和删除存储例程的能力 |
| CREATE | 影响create table命令的使用 |
| CREATE ROUTINE | 影响创建存储例程的能力 |
| CREATE TEMPORARY TABLES | 影响create temporary table命令的使用 |
| CREATE USER | 影响创建、删除、重命名和撤销用户权限的能力 |
| CREATE VIEW | 影响create view命令的使用 |
| DELETE | 影响delete命令的使用 |
| DROP | 影响drop table命令的使用 |
| EXECUTE | 影响用户运行存储过程的能力 |
| EVENT | 影响执行事件的能力 |
| FILE | 影响select into outfile和load data infile的使用 |
| GRANT OPTION | 影响用户委派权限的能力 |
| INDEX | 影响create index和drop index命令的使用 |
| INSERT | 影响insert命令的使用 |
| LOCK TABLES | 影响lock tables命令的使用 |
| PROCESS | 影响show processlist命令的使用 |
| REFERENCES | 未来 MySQL 特性的占位符 |
| RELOAD | 影响flush命令集的使用 |
| REPLICATION CLIENT | 影响用户查询从设备和主设备位置的能力 |
| REPLICATION SLAVE | 复制从属所需的权限 |
| SELECT | 影响select命令的使用 |
| SHOW DATABASES | 影响show databases命令的使用 |
| SHOW VIEW | 影响show create view命令的使用 |
| SHUTDOWN | 影响shutdown命令的使用 |
| SUPER | 影响管理员级命令的使用，如change master、kill和SET GLOBAL |
| TRIGGER | 影响执行触发器的能力 |
| UPDATE | 影响update命令的使用 |
| USAGE | 仅连接，不授予权限 |

在本节中，将详细介绍grant和revoke命令，随后是演示其用法的大量示例。

授予特权

当您需要为一个用户或一组用户分配新的权限时，您可以使用grant命令。这种权限分配可能小到只授予用户连接到数据库服务器的能力，也可能大到为几个同事提供 root MySQL 访问权限(当然不推荐，但也是可能的)。命令语法如下:

GRANT privilege_type [(column_list)] [, privilege_type [(column_list)] ...]
    ON {table_name | * | *.* | database_name.*}
    TO user_name [IDENTIFIED BY 'password']
        [, user_name [IDENTIFIED BY 'password'] ...]
    [REQUIRE {SSL|X509} [ISSUER issuer] [SUBJECT subject]]
    [WITH GRANT OPTION]

乍一看，grant语法可能看起来令人生畏，但实际上它使用起来非常简单。以下部分提供了一些示例，以帮助您更好地熟悉该命令。

注意

一旦执行了GRANT命令，该命令中授予的任何特权立即生效。

创建新用户并分配初始权限

第一个示例创建一个新用户，并为该用户分配一些特定于数据库的权限。用户ellie希望使用密码secret从 IP 地址 192.168.1.103 连接到数据库服务器。下面为她提供了在sakila数据库中找到的所有表的access, select, and insert特权:

mysql> grant select, insert on sakila.* to 'ellie'@'192.168.1.103'
    ->identified by 'secret';

在执行时，将修改两个特权表，即user和db表。因为user表负责访问验证和全局特权，所以必须插入一个新行来标识这个用户。但是，该行中的所有权限都将被禁用。为什么呢？因为grant命令只针对sakila数据库。除了启用Select_priv和Insert_priv列之外，db表将包含与将用户ellie映射到sakila数据库相关的用户信息。

向现有用户添加权限

现在假设用户ellie需要对驻留在sakila数据库中的所有表拥有update特权。这也是通过grant完成的:

mysql> grant update ON sakila.* TO 'ellie'@'192.168.1.103';

一旦执行，在db表中标识用户ellie@192.168.1.103的行被修改，使得Update_priv列被启用。请注意，在向现有用户添加权限时，不需要重新输入密码。

授予表级权限

现在假设除了前面定义的特权之外，用户ellie@192.168.1.103还需要位于sakila数据库中的两个表的delete特权，即category和language表。您可以限制特权，使该用户仅有权从这两个特定的表中删除数据，而不是授予该用户从该数据库的任何表中删除数据的全权。因为涉及两个表，所以需要两个grant命令:

mysql> grant delete on sakila.category to 'ellie'@'192.168.1.103';
Query OK, 0 rows affected (0.07 sec)
mysql> grant delete on sakila.language to 'ellie'@'192.168.1.103';
Query OK, 0 rows affected (0.01 sec)

因为这是一个特定于表的特权设置，所以只有tables_priv表会被触及。一旦执行，两个新行将被添加到tables_priv表中。这假设不存在预先存在的将类别和语言表映射到ellie@192.168.1.103的行。如果是这种情况，那些预先存在的行将被相应地修改，以反映新的特定于表的特权。

授予多个表级权限

前一个示例的变体是为用户提供多个权限，这些权限只限于给定的表。假设新用户will，从位于wjgilmore.com域内的多个地址连接，负责更新作者信息，因此只需要film表的select , insert , and update权限:

mysql> grant select, insert, delete on
    ->sakila.film TO will@'%.wjgilmore.com'
    ->identified by 'secret';

执行这个grant语句会在mysql数据库中产生两个新条目:在user表中的一个新行条目(同样，只是为will@%. wjgilmore.com提供访问权限)，以及在tables_priv表中的一个新条目，指定将应用于film表的新访问权限。请记住，因为特权只适用于单个表，所以只有一行被添加到tables_priv表中，其中Table_priv列被设置为Select, Insert, Delete。

授予列级权限

最后，考虑一个只影响表的列级特权的例子。假设您想授予用户威尔@192.168.1.105在sakila.film.title上的update权限:

mysql> grant update (title) on sakila.film TO 'will'@'192.168.1.105';

撤销特权

revoke命令负责从用户或用户组中删除先前授予的权限。语法如下:

REVOKE privilege_type [(column_list)] [, privilege_type [(column_list)] ...]
    ON {table_name | * | *.* | database_name.*}
    FROM user_name [, user_name ...]

与grant一样，理解该命令用法的最佳方式是通过一些例子。以下示例演示了如何撤销现有用户的权限，甚至删除现有用户。

撤销以前分配的权限

有时，您需要删除特定用户的一个或多个先前分配的权限。例如，假设您想要删除用户will@192.168.1.102对数据库 sakila:

mysql> revoke insert on sakila.* FROM 'will'@'192.168.1.102';

撤销表级权限

现在假设您想要删除用户will@192.168.1.102先前分配给位于数据库sakila中的表film的update和insert特权:

mysql> revoke insert, update on sakila.film FROM 'will'@'192.168.1.102';

注意，这个例子假设您已经向用户will@192.168.1.102授予了表级权限。revoke命令不会降级一个数据库级的grant(位于db表中的一个)，移除条目并在tables_priv表中插入一个条目。相反，在这种情况下，它只是从tables_priv表中删除对这些特权的引用。如果在tables_priv表中只引用了这两个特权，那么整行都会被删除。

撤销列级权限

作为最后一个撤销的例子，假设您之前已经授予用户will@192.168.1.102对位于sakila.film的列name的列级delete权限，现在您想要删除该权限:

mysql> revoke insert (title) ON sakila.film FROM 'will'@'192.168.1.102';

在所有这些使用revoke的例子中，如果在revoke命令中没有显式引用特权，用户will可能仍然能够在给定的数据库中行使一些特权。如果您想确保用户放弃所有权限，您可以撤销所有权限，如下所示:

mysql> revoke all privileges on sakila.* FROM 'will'@'192.168.1.102';

但是，如果您的目的是从mysql数据库中删除用户，请务必阅读下一节。

删除用户

关于revoke的一个常见问题是它如何删除用户。这个问题的简单答案是，根本不会。例如，假设您使用以下命令撤销特定用户的所有权限:

mysql> revoke all privileges ON sakila.* FROM 'will'@'192.168.1.102';

虽然这个命令确实删除了驻留在db表中与will@192.168.1.102和sakila数据库的关系相关的行，但是它没有从user表中删除该用户的条目，大概是为了以后可以恢复该用户而不必重置密码。如果您确定将来不再需要这个用户，您需要使用delete命令手动删除该行。

授予和撤销小费

以下列表提供了在使用grant和revoke时需要记住的各种提示:

• 您可以为尚不存在的数据库授予权限。

• 如果由grant命令识别的用户不存在，它将被创建。

• 如果您创建的用户不包含identified by子句，则登录时不需要密码。

• 如果现有用户被授予新的权限，并且grant命令带有一个identified by子句，用户的旧密码将被新密码替换。

• 表级权限仅支持以下权限类型:alter、create、create view、delete、drop、grant、index、insert、references、select、show view、trigger和update。

• 列级授权只支持以下特权类型:insert、references、select和update。

• 在grant命令中引用数据库名和主机名时，支持_和%通配符。因为_字符在 MySQL 数据库名称中也是有效的，所以如果在grant中需要的话，您需要用反斜杠对其进行转义。

• 要创建和删除用户，请务必使用create user和drop user命令。

• 您不能引用*.*来删除用户对所有数据库的权限。相反，每个都必须由单独的revoke命令显式引用。

查看权限

虽然您可以通过从权限表中选择适当的数据来检查用户的权限，但是随着权限表的增大，这种策略会变得越来越不实用。幸运的是，MySQL 提供了一种更方便的方法(实际上是两种)来检查用户特定的权限。这两者都将在本节中进行研究。

显示授予

show grants for命令显示授予特定用户的权限。举个例子，

mysql> show grants for 'ellie'@'192.168.1.102';

这将生成一个表，其中包含用户的授权信息(包括加密的密码)以及在全局、数据库、表和列级别授予的权限。

如果您想查看当前登录用户的权限，您可以使用current_user()功能，如下所示:

mysql> show grants for CURRENT_USER();

与grant和revoke命令一样，在使用show grants命令时，您必须参考用户名和发起主机，以便唯一地识别目标用户。

限制用户资源

监控资源使用总是一个好主意，但是当你在一个托管环境中提供 MySQL 时，比如一个 ISP，这一点尤其重要。如果您关心这样的问题，您会很高兴地了解到可以在每个用户的基础上限制 MySQL 资源的消耗。这些限制与其他权限一样，通过权限表进行管理。总共存在四个与资源使用相关的特权，它们都位于user表中:

• max_connections:决定用户每小时可以连接数据库的最大次数。

• max_questions:决定用户每小时可以执行的最大查询数(使用select命令)。

• max_updates:决定用户每小时可以执行的最大更新次数(使用insert、update and delete命令)。

• max_user_connections:确定给定用户可以保持的最大同时连接数。

考虑几个例子。第一个限制用户ellie@%. wjgilmore.com每小时的连接数为 3600，即平均每秒一个:

mysql> grant insert, select, update on books.* to
    ->'ellie'@'%.wjgilmore.com' identified by 'secret'
    ->with max_connections_per_hour 3600;

下一个示例将用户ellie@'%. wjgilmore.com每小时可以执行的更新总数限制为 10，000:

mysql> grant insert, select, update on books.* to 'ellie'@'%.wjgilmore.com'
    ->identified by 'secret' with max_updates_per_hour 10000;

安全的 MySQL 连接

客户机和 MySQL 服务器之间的数据流与任何其他典型的网络流量没有什么不同；它可能会被恶意的第三方截获甚至修改。有时这并不是一个真正的问题，因为数据库服务器和客户机通常位于同一个内部网络上，并且对于许多人来说，位于同一台机器上。但是，如果您的项目需求导致通过不安全的通道传输数据，那么您现在可以选择使用 MySQL 的内置安全特性，通过 SSL 和 X509 加密标准来加密该连接。

您可以通过登录到 MySQL 服务器并执行以下命令来验证 MySQL 是否准备好处理安全连接

mysql> show variables like 'have_openssl'

完成这些先决条件后，您需要创建或购买服务器证书和客户端证书。完成这两项任务的过程超出了本书的范围。你可以在互联网上获得关于这些过程的信息。随着像 https://letsencrypt.org 这样的免费服务的出现，获取 SSL 证书变得越来越容易。

常见问题

当用户开始研究 MySQL 的安全连接特性时，出现了几个反复出现的问题。

我使用 MySQL 作为我的 web 应用的后端，我使用 HTTPS 来加密进出网站的流量。我还需要加密到 MySQL 服务器的连接吗？

这取决于数据库服务器是否与 web 服务器位于同一台计算机上。如果是这种情况，那么只有当您认为您的机器本身不安全时，加密才有可能是有益的。如果数据库服务器驻留在单独的服务器上，那么数据可能会不安全地从 web 服务器传输到数据库服务器，因此需要加密。关于加密的使用没有固定的规则。只有在仔细权衡安全性和性能因素后，您才能得出结论。

我如何知道流量确实被加密了？

确保 MySQL 流量加密的最简单方法是创建一个需要 SSL 的用户帐户，然后通过提供该用户的凭据和有效的 SSL 证书，尝试连接到启用了 SSL 的 MySQL 服务器。如果出现问题，您会收到“拒绝访问”错误。

加密的 MySQL 流量在哪个端口上流动？

无论您是以加密还是不加密的方式进行通信，端口号都保持不变(3306)。

授予期权

有许多授权选项可以决定用户的 SSL 要求。本节将介绍这些选项。

需要 SSL

require ssl grant 选项强制用户通过 SSL 连接。任何以不安全方式连接的尝试都将导致“拒绝访问”错误。下面是一个例子:

mysql> grant insert, select, update on sakila.* TO 'will'@'192.168.1.12'
     ->identified by 'secret' require ssl;

需要 X509

require x509 grant 选项强制用户提供有效的认证机构(CA)证书。如果您想用 CA 证书验证证书签名，这将是必需的。请注意，该选项不会导致 MySQL 考虑来源、主题或发布者。下面是一个例子:

mysql> grant insert, select, update on sakila.* to 'will'@'192.168.1.12'
     ->identified by 'secret' require ssl require x509;

注意，这个选项也没有指定哪些 ca 是有效的，哪些是无效的。任何验证证书的 CA 都被认为是有效的。如果您想限制哪些 ca 被认为是有效的，请参阅下一个授权选项。

要求发行人

require issuer grant 选项强制用户提供由有效的 CA 发行者发行的有效证书。此外，还必须包括其他一些信息，包括原产国、原产州、原产城市、证书所有者的姓名和证书联系人。下面是一个例子:

mysql> grant insert, select, update on sakila.* TO 'will'@'192.168.1.12'
   ->identified by 'secret' require ssl require issuer 'C=US, ST=Ohio,
   ->L=Columbus, O=WJGILMORE,
   ->OU=ADMIN, CN=db.wjgilmore.com/Email=admin@wjgilmore.com'

要求主题

require subject授权选项强制用户提供一个有效的证书，包括一个有效的证书“主题”下面是一个例子:

mysql> grant insert, select, update on sakila.* TO 'will'@'192.168.1.12'
    ->identified by 'secret' require ssl require subject
    ->'C=US, ST=Ohio, L=Columbus, O=WJGILMORE, OU=ADMIN,
    ->CN=db.wjgilmore.com/Email=admin@wjgilmore.com'

需要密码

require cipher grant 选项通过强制用户使用特定密码进行连接来强制使用最新的加密算法。目前可用的选项包括 EDH、RSA、DES、CBC3 和 SHA。下面是一个例子:

mysql>grant insert, select, update on sakila.* TO 'will'@'192.168.1.12'
      ->identified by 'secret' require ssl require cipher 'DES-RSA';

SSL 选项

服务器和连接客户端都使用本节介绍的选项来确定是否应该使用 SSL，如果应该使用，则确定证书和密钥文件的位置。

- ssl

--ssl选项表示 MySQL 服务器应该允许 SSL 连接。与客户端结合使用时，它表示将使用 SSL 连接。请注意，包含此选项并不确保也不要求使用 SSL 连接。事实上，测试表明，启动 SSL 连接甚至不需要选项本身。相反，这里介绍的附带标志决定了 SSL 连接是否成功启动。

–类似 ssl

• ssl-ca选项指定包含可信 SSL 证书颁发机构列表的文件的位置和名称。举个例子，

--ssl-ca=/home/jason/openssl/cacert.pem

–ssl-capath

• ssl-capath选项指定存储隐私增强邮件(PEM)格式的可信 SSL 证书的目录路径。

–ssl-true

• ssl-cert选项指定用于建立安全连接的 SSL 证书的位置和名称。例如，

--ssl-cert=/home/jason/openssl/mysql-cert.pem

- ssl 密码

--ssl-cipher选项指定允许哪些加密算法。密码列表语法与以下命令使用的语法相同:

%>openssl ciphers

例如，要仅允许 TripleDES 和 Blowfish 加密算法，该选项设置如下:

--ssl-cipher=des3:bf

- ssl 密钥

• ssl-key选项指定用于建立安全连接的 SSL 密钥的位置和名称。例如，

--ssl-key=/home/jason/openssl/mysql-key.pem

在接下来的三节中，您将学习如何在命令行和my.cnf文件中使用这些选项。

启动支持 SSL 的 MySQL 服务器

一旦有了服务器和客户机证书，就可以像这样启动支持 SSL 的 MySQL 服务器:

%>./bin/mysqld_safe --user=mysql --ssl-ca=$SSL/cacert.pem \
 >--ssl-cert=$SSL/server-cert.pem --ssl-key=$SSL/server-key.pem &

$SSL指指向 SSL 证书存储位置的路径。

使用支持 SSL 的客户端进行连接

然后，您可以使用以下命令连接到启用了 SSL 的 MySQL 服务器:

%>mysql --ssl-ca=$SSL/cacert.pem --ssl-cert=$SSL/client-cert.pem \
->--ssl-key=$SSL/client-key.pem -u jason -h www.wjgilmore.com -p

同样，$SSL指的是指向 SSL 证书存储位置的路径。

将 SSL 选项存储在 my.cnf 文件中

当然，您不必通过命令行传递 SSL 选项。相反，您可以将它们放在一个my.cnf文件中。下面是一个示例my.cnf文件:

[client]
ssl-ca     = /home/jason/ssl/cacert.pem
ssl-cert   = /home/jason/ssl/client-cert.pem
ssl-key    = /home/jason/ssl/client-key.pem

[mysqld]
ssl-ca     = /usr/local/mysql/ssl/ca.pem
ssl-cert   = /usr/local/mysql/ssl/cert.pem
ssl-key    = /usr/local/mysql/openssl/key.pem

摘要

一次不请自来的数据库入侵可能会抹去数月的工作成果和不可估量的价值。因此，尽管本章涵盖的主题通常缺乏其他专业技能的魅力，例如创建数据库连接和改变表结构，但是花时间彻底理解这些安全主题的重要性怎么强调都不为过。强烈建议您花足够的时间了解 MySQL 的安全特性，因为它们应该在所有 MySQL 驱动的应用中定期出现。

下一章将介绍 PHP 的 MySQL 库，向您展示如何通过 PHP 脚本操作 MySQL 数据库数据。那一章之后是 MySQLi 库的介绍，如果你运行的是 PHP 5 和 MySQL 4.1 或更高版本，应该会用到它。