set和show语法之一

核心提示：译者：叶金荣（Email:），手册来源：MySQL(和PHP搭配之最佳组合)手册版本 5.0.20，set和show语法之一，出处：http://iMySQL(和PHP搭配之最佳组合).cn，转载请注明译者和出处，它是在 MySQL(和PHP搭配之最佳组合) 3.23.20 中新增的，MySQL(和PHP搭配之最佳组合

　　译者：叶金荣（Email:），手册来源：MySQL(和PHP搭配之最佳组合)手册版本 5.0.20，出处：http://iMySQL(和PHP搭配之最佳组合).cn，转载请注明译者和出处，并且不能用于商业用途，违者必究。
14.5.3 SET 和 SHOW 语法

SET 可以用于设置各种变量或者选项。

SHOW 可以以各种形式描述数据库系统的信息，诸如数据表，字段，服务器状态等等。大致有以下这些方法：

SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern']
SHOW CREATE DATABASE db_name
SHOW CREATE TABLE tbl_name
SHOW DATABASES [LIKE 'pattern']
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW INNODB STATUS
SHOW [BDB] LOGS
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW STATUS [LIKE 'pattern']
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern']
SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern']
SHOW WARNINGS [LIMIT [offset,] row_count]

如果 SHOW 语法中包括 LIKE 'pattern' 分句的话，'pattern' 是一个可以包括 SQL 语法中‘ % '和‘ _ '等通配符的字符串。这个模式语句对于取得相应的结果十分有用。

请注意，还有以下几种不同的标识方式：

* SET PASSWOR 语句用于设定帐户的密码，详情请看“14.5.1.3 SET PASSWORD Syntax”。
* SHOW 语句有以下几种方式用于描述复制主服务器和从服务器的信息：
*

SHOW BINLOG EVENTS
SHOW MASTER LOGS
SHOW MASTER STATUS
SHOW SLAVE HOSTS
SHOW SLAVE STATUS


这些格式的 SHOW 详细描述详情请看“14.6 Replication Statements”。

14.5.3.1 SET 语法

SET variable_assignment [, variable_assignment] ...

variable_assignment:
user_var_name = expr
| [GLOBAL | SESSION] system_var_name = expr
| @@[global. | session.]system_var_name = expr

SET 可以设置各种不同的变量类型，这些变量可以影响着服务器或者客户端的参数。它可以帮助你设置用户变量或者是系统变量。 在 MySQL(和PHP搭配之最佳组合) 4.0.3 中，我们增加了 GLOBAL 和 SESSION 选项，并且可以在运行时动态修改系统中最重要的参数。这些可以被动态修改的系统参数详情请看“5.2.3.1 Dynamic System Variables”中会被详细描述到。 在老版本的 MySQL(和PHP搭配之最佳组合) 中，SET 其实是 SET OPTION 的代替，它少了 OPTION 这个参数，不过现在不推荐的使用方法。 以下这个例子演示了几种使用设置变量的方法。

用户变量写成 @var_name，用以下方法来设置：

SET @var_name = expr;

用户变量的特性说明将在章节“10.3 User Variables”中提到。

系统变量在 SET 语法中可以以 var_name 的方式来引用。变量名前面用 GLOBAL 或 @@global. 来表示这是一个全局变量，用 SESSION , @@session. 或 @@ 来表示这是一个会话变量。 LOCAL 和 @@local. 其实是 SESSION 和 @@session. 的同义。如果变量名前面没有附加的修饰词的话，那么 SET 只修改会话变量。

以 @@var_name这种格式来表示系统变量，在 MySQL(和PHP搭配之最佳组合) 中是可以支持的，以兼容其它数据库系统。

如果你想在同一个语句中来修改多个系统变量，最后一次出现的 GLOBAL 或 SESSION 将作为该语句中没有修饰词的变量的默认选项。

SET sort_buffer_size=10000;
SET @@local.sort_buffer_size=10000;
SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000;
SET @@sort_buffer_size=1000000;
SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;

如果你设置了系统变量时用 SESSION （默认的） 选项，那么它的值将在会话结束前或者直到你改变它之前一直有效。如果设置系统变量是用 GLOBAL 选项，这必须要求你具备 SUPER （超级） 权限，它的值从新的连接开始到系统重启前一直有效。如果你想要一个变量永久生效的话，那么可以把它放到配置文件中。详情请看“4.3.2 Using Option Files”。 为了防止操作错误，例如如果你想用 SET GLOBAL 修改一个只能用 SET SESSION 来修改的变量，或者不是用 SET GLOBAL 的方式来修改一个全局变量时， MySQL(和PHP搭配之最佳组合) 就会报告一个错误。 如果你想让一个 SESSION （会话） 变量成为一个 GLOBAL 变量，或者想让 GLOBAL 变量的值作为 MySQL(和PHP搭配之最佳组合) 的默认值被内嵌编译进去的话，你可以设置它为 DEFAULT （默认值） 。以下的这 2 个例子都是同样的设置会话变量 max_join_size 的值称为全局变量的值：

SET max_join_size=DEFAULT;
SET @@session.max_join_size=@@global.max_join_size;

你可以通过 SHOW VARIABLES 命令列出绝大部分的系统变量。详情请看“14.5.3.20 SHOW VARIABLES Syntax”的语法 。如果想要通过模式匹配的方式来获取变量列表的话，可以使用 LIKE 分句：

SHOW VARIABLES LIKE 'max_join_size';
SHOW GLOBAL VARIABLES LIKE 'max_join_size';

You can also get the value for a specific value by using the @@[global.|local.]var_name syntax with SELECT: 你还可以使用 SELECT 来取得 @@[global.|local.]var_name 对应的值：

SELECT @@max_join_size, @@global.max_join_size;

在通过 SELECT @@var_name ( 这样的话，就无需指定是 global 还是 session 抑或是 local 了 ) 来取得变量的值时， MySQL(和PHP搭配之最佳组合) 会返回 SESSION 变量的值，如果它不存在，则返回 GLOBAL 变量的值。 以下所列的是那些非标准或者是在章节“5.2.3 Server System Variables”中找没有列出来的变量。尽管这些变量用 SHOW VARIABLES 并不显示出来，但你可以用 SELECT ( CHARACTER SET 和 SET NAMES 除外 ) 来取得它们的值。例如：

MySQL(和PHP搭配之最佳组合)> SELECT @@AUTOCOMMIT;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+

AUTOCOMMIT = {0 | 1}
设置 autocommit （自动提交）模式。若设置为 1 ，则所有对数据表的操作都会立即生效。若设置为 0 ，则必须执行 COMMIT 以提交事务或者执行 ROLLBACK 取消事务。如果把 AUTOCOMMIT 由 0 改成 1 ， MySQL(和PHP搭配之最佳组合) 会自动提交任何一个开放的事务。开始一个事务的另一个方法可以是执行 START TRANSACTION 或 BEGIN 语句。详情请看“14.4.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax”。
BIG_TABLES = {0 | 1}
如果被设置为 1，则所有的临时表将存放在磁盘里而非内存。这会使速度变得稍微慢了点，但是在执行 SELECT 操作时需要一个大临时表的情况下就不再会报告 The table tbl_name is full 错误了。新的连接时，它的默认值是 0（在内存中存放临时表） 。从 MySQL(和PHP搭配之最佳组合) 4.0 起，你无需设置这个变量，因为 MySQL(和PHP搭配之最佳组合) 会自动在必须的情况下将内存（存储）表转换为硬盘（存储）表。这个变量现在的名字是 SQL_BIG_TABLES 。
CHARACTER SET {charset_name | DEFAULT}
它用于将所有的字符串转换成指定的字符串和将客户端取得的字符串转成指定的字符串。在 MySQL(和PHP搭配之最佳组合) 4.1 以前，唯一允许的字符集是 cp1251_koi8 ，不过你也可以通过修改 MySQL(和PHP搭配之最佳组合) 发布的源代码中的 `sql/convert.cc' 文件来增加新的转换字符集。从 MySQL(和PHP搭配之最佳组合) 4.1.1 开始，SET CHARACTER SET 将设置 3 个会话变系统量： character_set_client 和 character_set_results 会被设置成指定的字符集， character_set_connection 的值被设置成 character_set_database 的值。默认的转换字符集可以被重置为值 DEFAULT （默认值） 。请注意，SET CHARACTER SET 的语法和其它的大多数设置选项不一样。
FOREIGN_KEY_CHECKS = {0 | 1}
如果它设置为 1 （默认值） ，则 InnoDB 表都会检查外键约束关系。如果设置为 0 ，则会略过，不做检查。禁止外键约束关系检查在重新加载顺序和实际所要求的父子关系顺序不一致的 InnoDB 表时非常有用。这个变量是在 MySQL(和PHP搭配之最佳组合) 3.23.52 中新增的。详情请看“16.7.4 FOREIGN KEY Constraints”。
IDENTITY = value
这个变量和变量 LAST_INSERT_ID 实际上是一样的。它的存在是为了与其它数据库系统兼容。从 MySQL(和PHP搭配之最佳组合) 3.23.25 开始，可以通过执行 SELECT @@IDENTITY 来获得它的值。从 MySQL(和PHP搭配之最佳组合) 4.0.3 开始，还可以执行 SET IDENTITY 来改变它的值。
INSERT_ID = value
设置它的值，则可以作为在执行 INSERT 或 ALTER TABLE 语句中要插入一个 AUTO_INCREMENT 值时的下一个值。它主要用于 binary log 。
LAST_INSERT_ID = value
设置的值就是 LAST_INSERT_ID() 得到的值。当数据表被更新是它的值会存放在 binary log 中，可以执行 LAST_INSERT_ID() 语句来取得它的值。设置它的值不会改变 C API 函数 MySQL(和PHP搭配之最佳组合)_insert_id() 返回的结果值。
NAMES {'charset_name' | DEFAULT}
SET NAMES 设置了 3 个会话变量 character_set_client , character_set_connection 和 character_set_results 为给定的字符集。默认的映射关系可以使用值 DEFAULT 来重新设定。请注意， SET NAMES 语法也不同于大多数其它的设置选项。它是自从 MySQL(和PHP搭配之最佳组合) 4.1.0 以后才开始有的。
SQL_AUTO_IS_NULL = {0 | 1}
如果被设置为 1 ( 默认值 ) ，则可以通过以下语句来获得某个包含 AUTO_INCREMENT 类型字段的表的最后一个插入语句：

WHERE auto_increment_column IS NULL

这种做法在某些 ODBC 程序中会被用到，例如 Access 。 SQL_AUTO_IS_NULL 变量是在 MySQL(和PHP搭配之最佳组合) 3.23.52 中新增的。
SQL_BIG_SELECTS = {0 | 1}
如果设置为 0 ，则 MySQL(和PHP搭配之最佳组合) 会放弃那些可能会耗费很长时间（是指那些通过优化程序估计到需要检查的行数会超过 max_join_size 设定值的情况）的 SELECT 语句。这在有不可取的 WHERE 语句出现时十分有用。 每个新连接的 SQL_BIG_SELECTS 默认值为 1 ，以允许执行所有的 SELECT 语句。如果设置系统变量 max_join_size 为非默认值（ DEFAULT ），则 SQL_BIG_SELECTS 也会被自动设置成为 0。
SQL_BUFFER_RESULT = {0 | 1}
SQL_BUFFER_RESULT 强制将 SELECT 语句查询的结果放在临时表中。这可以让 MySQL(和PHP搭配之最佳组合) 尽快释放加载表上的锁，同时还有助于改善需要较长时间向客户端发送结果的情况。这个变量是在 MySQL(和PHP搭配之最佳组合) 3.23.13 中新增的。
SQL_LOG_BIN = {0 | 1}
如果设置为 0，就不再记录任何 binary log 了。必须要有 SUPER （超级） 权限方可修改它的值。这个变量是在 MySQL(和PHP搭配之最佳组合) 3.23.13 中新增的。
SQL_LOG_OFF = {0 | 1}
如果设置为 1 ，系统将不记录任何普通的查询日志。必须要有 SUPER （超级） 权限方可修改它的值。
SQL_LOG_UPDATE = {0 | 1}
如果设置为 0 ，update log 将不记录任何日志。必须要有 SUPER （超级） 权限方可修改它的值。这个变量是在 MySQL(和PHP搭配之最佳组合) 3.22.5 中新增的。从 MySQL(和PHP搭配之最佳组合) 5.0.0 开始，它已经建议不再使用，而变成了 SQL_LOG_BIN （ 详情请看“C.1.3 Changes in release 5.0.0 (22 Dec 2003: Alpha” ） 。
SQL_QUOTE_SHOW_CREATE = {0 | 1}
如果设置为 1 ，则在执行 SHOW CREATE TABLE 时就会把数据表名和字段用引号引起来；如果设置为 0 ，就不再引用了。这个选项默认是打开的，因为在数据库复制时需要所有的表名和字段名都必须引用起来才行。这个选项是在 MySQL(和PHP搭配之最佳组合) 3.23.26 中新增的。详细的请查看“14.5.3.6 SHOW CREATE TABLE Syntax”。
SQL_SAFE_UPDATES = {0 | 1}
如果设置为 1 ，则 MySQL(和PHP搭配之最佳组合) 会放弃那些在 WHERE 或 LIMIT 分句中没有使用键的 UPDATE 或 DELETE 语句。这就可能会捕获那些没有正确使用键并且可能会删除很多记录的 UPDATE 或 DELETE 语句。这个变量是在 MySQL(和PHP搭配之最佳组合) 3.22.32 中新增的。
SQL_SELECT_LIMIT = {value | DEFAULT}
它决定了执行 SELECT 语句时返回的最大记录数。新连接的默认设置值是“ unlimited （无限）”。如果它被改变了，可以设定重新设 SQL_SELECT_LIMIT 的值定为 DEFAULT 以将它恢复为默认值。当 SELECT 语句中有 LIMIT 分句时， LIMIT 优先级高于 SQL_SELECT_LIMIT 的值。
SQL_WARNINGS = {0 | 1}
它决定了在执行单行 INSERT 语句发生错误的情况下，是否要报告错误信息。它的默认值是 0 ，如果设置为 1 ，则会在发生错误时报告错误信息。这个变量是在 MySQL(和PHP搭配之最佳组合) 3.22.11 中新增的。
TIMESTAMP = {timestamp_value | DEFAULT}
它可以设定系统时间。它用于取得在用 binary log 恢复记录时的原始时间戳。 timestamp_value 必须是 Unix 时间戳，而不是 MySQL(和PHP搭配之最佳组合) 时间戳。
UNIQUE_CHECKS = {0 | 1}
如果设定为 1 （默认值），就会对 InnoDB 表的辅助索引做唯一性检查。如果设置为 0 ，就不再做唯一性检查。这个变量是在 MySQL(和PHP搭配之最佳组合) 3.23.52 中新增的。详情请看“16.7.4 FOREIGN KEY Constraints”。

14.5.3.2 SHOW CHARACTER SET 语法

SHOW CHARACTER SET [LIKE 'pattern']

SHOW CHARACTER SET 列出了所有可用的字符集。它有一个可选项是 LIKE 分句以示匹配何种字符集。例如：

MySQL(和PHP搭配之最佳组合)> SHOW CHARACTER SET LIKE 'latin%';
+---------+-----------------------------+-------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+-----------------------------+-------------------+--------+
| latin1 | ISO 8859-1 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
+---------+-----------------------------+-------------------+--------+

Maxlen 字段告诉我们储存一个字符使用的最大字节数。

SHOW CHARACTER SET 是在 MySQL(和PHP搭配之最佳组合) 4.1.0 中新增的。
14.5.3.3 SHOW COLLATION 语法

SHOW COLLATION [LIKE 'pattern']


SHOW COLLATION 的结果包括了所有可用的字符集。它有一个可选项是 LIKE 分句以示匹配何种字符集。例如：

MySQL(和PHP搭配之最佳组合)> SHOW COLLATION LIKE 'latin1%';
+-------------------+---------+----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+-------------------+---------+----+---------+----------+---------+
| latin1_german1_ci | latin1 | 5 | | | 0 |
| latin1_swedish_ci | latin1 | 8 | Yes | Yes | 0 |
| latin1_danish_ci | latin1 | 15 | | | 0 |
| latin1_german2_ci | latin1 | 31 | | Yes | 2 |
| latin1_bin | latin1 | 47 | | Yes | 0 |
| latin1_general_ci | latin1 | 48 | | | 0 |
| latin1_general_cs | latin1 | 49 | | | 0 |
| latin1_spanish_ci | latin1 | 94 | | | 0 |
+-------------------+---------+----+---------+----------+---------+

Default 字段表示该校对字符集是否是系统默认的字符集。 Compiled 字段表示该校对字符集是否预编译到服务器中了。 Sortlen 字段表示需要多少内存以存储该校对字符集的字符串表示。 SHOW COLLATION 是在 MySQL(和PHP搭配之最佳组合) 4.1.0 中新增的。
14.5.3.4 SHOW COLUMNS 语法

SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern']

SHOW COLUMNS 列出了给定表的所有字段。请注意，如果看到的字段类型和你以前用 CREATE TABLE 创建的字段类型不同的话，可能是 MySQL(和PHP搭配之最佳组合) 有时候会在创建或修改表结构时修改字段类型。什么情况下 MySQL(和PHP搭配之最佳组合) 会改变字段类型详情请看“14.2.6.1 Silent Column Specification Changes”所述。 从 MySQL(和PHP搭配之最佳组合) 3.23.32 开始，就可以使用关键字 FULL 了。它使得输出的结果包括了当前用户对每个字段所具备的权限。从 MySQL(和PHP搭配之最佳组合) 4.1 开始，关键字 FULL 也会令每个字段的注释显示出来。在 tbl_name FROM db_name 语法中，还可以选择是否使用 db_name.tbl_name 的格式。以下的这 2 个语句是一样的：

MySQL(和PHP搭配之最佳组合)> SHOW COLUMNS FROM mytable FROM mydb;
MySQL(和PHP搭配之最佳组合)> SHOW COLUMNS FROM mydb.mytable;

SHOW FIELDS 和 SHOW COLUMNS 是一样的。同时还可以通过命令 MySQL(和PHP搭配之最佳组合)show db_name tbl_name 来列出所有的字段。

DESCRIBE 语句和 SHOW COLUMNS 显示的结果信息类似。详情请看“14.3.1 DESCRIBE Syntax (Get Information About Columns)”。
14.5.3.5 SHOW CREATE DATABASE 语法

SHOW CREATE DATABASE db_name

根据给定的数据库显示一个完整的 CREATE DATABASE 语句。它是在 MySQL(和PHP搭配之最佳组合) 4.1 中新增的。

MySQL(和PHP搭配之最佳组合)> SHOW CREATE DATABASE test\G
*************************** 1. row ***************************
Database: test
Create Database: CREATE DATABASE `test`
/*!40100 DEFAULT CHARACTER SET latin1 */

14.5.3.6 SHOW CREATE TABLE 语法

SHOW CREATE TABLE tbl_name

根据给定的数据表显示一个完整的 CREATE TABLE 语句。它是在 MySQL(和PHP搭配之最佳组合) 3.23.20 中新增的。

MySQL(和PHP搭配之最佳组合)> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE t (
id INT(11) default NULL auto_increment,
s char(60) default NULL,
Prima(最完善的虚拟主机管理系统)RY KEY (id)
) TYPE=MyISAM

SHOW CREATE TABLE 根据参数 SQL_QUOTE_SHOW_CREATE 的值来决定是否要把表名和字段名用引号引用起来。详情请看“14.5.3.1 SET Syntax”。