Linux · 2013-12-12

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 PASSWORDSyntax”。
    • 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 4.0.3 中，我们增加了 GLOBAL 和 SESSION 选项，并且可以在运行时动态修改系统中最重要的参数。这些可以被动态修改的系统参数详情请看“5.2.3.1 Dynamic System Variables”中会被详细描述到。
在老版本的 MySQL 中，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 中是可以支持的，以兼容其它数据库系统。

如果你想在同一个语句中来修改多个系统变量，最后一次出现的 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 就会报告一个错误。

如果你想让一个 SESSION （会话） 变量的值设定成为一个 GLOBAL 变量的值，或者想让 GLOBAL 变量的值变成MySQL内嵌编译的默认值的话，你可以设置它为DEFAULT （默认值） 。以下的这 2 个例子都是同样的设置会话变量max_join_size 的值成为全局变量的值：

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

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

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

你还可以使用 SELECT @@[global.|local.]var_name 来取得指定变量的值：

SELECT @@max_join_size, @@global.max_join_size;

在通过 SELECT @@var_name ( 这样的话，就无需指定是 global 还是 session 抑或是 local 了 ) 来取得变量的值时， MySQL 会返回 SESSION 变量的值，如果它不存在，则返回 GLOBAL 变量的值。

以下所列的是那些非标准或者是在章节“5.2.3 Server System Variables”中找没有列出来的变量。尽管这些变量用 SHOW VARIABLES 并不显示出来，但你可以用 SELECT (CHARACTER SET 和 SET NAMES 除外 ) 来取得它们的值。例如：

mysql> SELECT @@AUTOCOMMIT;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

 

 

AUTOCOMMIT = {0 | 1}
设置 autocommit （自动提交）模式。若设置为 1 ，则所有对数据表的操作都会立即生效。若设置为 0 ，则必须执行 COMMIT 以提交事务或者执行 ROLLBACK取消事务。如果把 AUTOCOMMIT 由 0 改成 1 ， MySQL 会自动提交任何一个开放的事务。开始一个事务的另一个方法可以是执行 START TRANSACTION 或BEGIN 语句。详情请看“14.4.1 START TRANSACTION, COMMIT, andROLLBACK Syntax”。 

BIG_TABLES = {0 | 1}
如果被设置为 1，则所有的临时表将存放在磁盘里而非内存。这会使速度变得稍微慢了点，但是在执行 SELECT 操作时需要一个大临时表的情况下就不再会报告The table tbl_name is full 错误了。对于新连接，它的默认值是 0（在内存中存放临时表） 。从 MySQL 4.0 起，你无需设置这个变量，因为 MySQL 会自动在需要的情况下将内存存储的表转换为硬盘存储的表。这个变量现在的名字是 SQL_BIG_TABLES 。 

CHARACTER SET {charset_name | DEFAULT}
它用于将从客户端取得的以及发往客户端的字符串都映射成指定的字符串。在 MySQL 4.1 以前，唯一允许的字符集是 cp1251_koi8 ，不过你也可以通过修改 MySQL 发布的源代码中的 `sql/convert.cc' 文件来增加新的转换字符集。从 MySQL 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 3.23.52 中新增的。详情请看“16.7.4 FOREIGN KEY Constraints”。
IDENTITY = value
这个变量和变量 LAST_INSERT_ID 实际上是一样的。它的存在是为了与其它数据库系统兼容。从 MySQL 3.23.25 开始，可以通过执行 SELECT @@IDENTITY来获得它的值。从 MySQL 4.0.3 开始，还可以执行 SET IDENTITY 来改变它的值。 

INSERT_ID = value
修改它的值可以作为在执行 INSERT 或 ALTER TABLE 语句中要插入一个AUTO_INCREMENT 值时的下一个值。它主要用于二进制日志(binary log)。 

LAST_INSERT_ID = value
修改它的值可以作为 LAST_INSERT_ID() 的返回值。当数据表被更新时它的值会存放在二进制日志中，可以执行 LAST_INSERT_ID() 语句来取得它的值。设置它的值不会改变 C API 函数 mysql_insert_id() 返回的结果值。 

NAMES {'charset_name' | DEFAULT}
SET NAMES 设置了 3 个会话变量 character_set_client ,character_set_connection 和 character_set_results 为指定的字符集。默认的映射关系可以设定为 DEFAULT。请注意， SET NAMES 语法也不同于大多数其它的设置选项。它是自从 MySQL 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 3.23.52 中新增的。

 

SQL_BIG_SELECTS = {0 | 1}
如果设置为 0 ，则 MySQL 会放弃那些可能会耗费很长时间（是指那些通过优化程序估计到需要检查的行数会超过 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 尽快释放加载表上的锁，同时还有助于改善向客户端发送结果需要较长时间的情况。这个变量是在 MySQL 3.23.13 中新增的。
 

SQL_LOG_BIN = {0 | 1}
如果设置为 0，就不再记录任何二进制日志了。必须要有 SUPER （超级） 权限方可修改它的值。这个变量是在 MySQL 3.23.13 中新增的。 

SQL_LOG_OFF = {0 | 1}
如果设置为 1 ，系统将不记录任何普通的查询日志。必须要有 SUPER （超级） 权限方可修改它的值。 

SQL_LOG_UPDATE = {0 | 1}
如果设置为 0 ，更新日志将不记录任何日志。必须要有 SUPER （超级） 权限方可修改它的值。这个变量是在 MySQL 3.22.5 中新增的。从 MySQL 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 3.23.26 中新增的。详细的请查看“14.5.3.6 SHOW CREATE TABLE Syntax”。 

SQL_SAFE_UPDATES = {0 | 1}
如果设置为 1 ，则 MySQL 会放弃那些在 WHERE 或 LIMIT 分句中没有使用键的UPDATE 或 DELETE 语句。这就可能会捕获那些没有正确使用键并且可能会删除很多记录的 UPDATE 或 DELETE 语句。这个变量是在 MySQL 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 语句(译者注：一次只有一个 INSERT 语句)发生错误的情况下，是否要报告错误信息。它的默认值是 0 ，如果设置为 1 ，则会在发生错误时报告错误信息。这个变量是在 MySQL 3.22.11 中新增的。 

TIMESTAMP = {timestamp_value | DEFAULT}
它可以设定系统时间。它用于取二进制日志用于恢复记录时来取得原始时间戳。timestamp_value 必须是 Unix 时间戳，而不是 MySQL 时间戳。 

UNIQUE_CHECKS = {0 | 1}
如果设定为 1 （默认值），就会对 InnoDB 表的第二索引做唯一性检查。如果设置为 0 ，就不再做唯一性检查。这个变量是在 MySQL 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> 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 4.1.0 中新增的。

14.5.3.3 SHOW COLLATION 语法

SHOW COLLATION [LIKE 'pattern']

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

mysql> 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 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 有时候会在创建或修改表结构时修改字段类型。什么情况下 MySQL 会改变字段类型详情请看“14.2.6.1 Silent Column Specification Changes”。

从 MySQL 3.23.32 开始，就可以使用关键字 FULL 了。它使得输出的结果包括了当前用户对每个字段所具备的权限。从 MySQL 4.1 开始，关键字 FULL 也会令每个字段的注释显示出来。

还可以选择是否使用 db_name.tbl_name 的语法格式。以下的这2个语句是一样的：

mysql> SHOW COLUMNS FROM mytable FROM mydb;
mysql> SHOW COLUMNS FROM mydb.mytable;

SHOW FIELDS 和 SHOW COLUMNS 是一样的。同时还可以通过命令 mysqlshowdb_name tbl_name 来列出所有的字段。

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

 

14.5.3.5 SHOW CREATE DATABASE 语法

SHOW CREATE DATABASE db_name

根据给定的数据库显示一个完整的 CREATE DATABASE 语句。它是在 MySQL 4.1 中新增的。

mysql> 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 3.23.20 中新增的。

mysql> 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,
  PRIMARY KEY (id)
) TYPE=MyISAM default charset utf8

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

标签：