mysql set 语法_MYSQL SET 和 SHOW 语法

最新推荐文章于 2021-12-06 15:27:56 发布

風来坊Furaibo

最新推荐文章于 2021-12-06 15:27:56 发布

阅读量1.4k

点赞数

文章标签： mysql set 语法

本文链接：https://blog.csdn.net/weixin_33322743/article/details/113229017

版权

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

SHOW语句有以下几种方式用于描述复制主服务器和从服务器的信息：

SHOW BINLOG EVENTS

SHOW MASTER LOGS

SHOW MASTER STATUS

SHOW SLAVE HOSTS

SHOW SLAVE STATUS

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选项，并且可以在运行时动态修改系统中最重要的参数。这些可以被动态修改的系统参数详情请看在老版本的MySQL中，SET其实是 SET OPTION的代替，它少了 OPTION这个参数，不过现在不推荐的使用方法。

以下这个例子演示了几种使用设置变量的方法。

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

SET @var_name = expr;

用户变量的特性说明将在章节

系统变量在 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(超级) 权限，它的值从新的连接开始到系统重启前一直有效。如果你想要一个变量永久生效的话，那么可以把它放到配置文件中。详情请看

为了防止操作错误，例如如果你想用 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命令列出绝大部分的系统变量。详情请看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变量的值。

以下所列的是那些非标准或者是在章节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语句。详情请看

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中新增的。详情请看

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( 详情请看

SQL_QUOTE_SHOW_CREATE = {0 | 1}

如果设置为 1，则在执行 SHOW CREATE TABLE时就会把数据表名和字段用引号引起来；如果设置为 0，就不再引用了。这个选项默认是打开的，因为在数据库同步时需要所有的表名和字段名都必须引用起来才行。这个选项是在MySQL 3.23.26中新增的。详细的请查看

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中新增的。详情请看

SHOW CHARACTER SET [LIKE 'pattern']

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

mysql> SHOW CHARACTER SET LIKE 'latin%';

+---------+-----------------------------+-------------------+--------+

+---------+-----------------------------+-------------------+--------+

+---------+-----------------------------+-------------------+--------+

Maxlen字段显示了储存一个字符使用的最大字节数。

SHOW CHARACTER SET是在MySQL 4.1.0中新增的。

SHOW COLLATION [LIKE 'pattern']

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

mysql> SHOW COLLATION LIKE 'latin1%';

+-------------------+---------+----+---------+----------+---------+

+-------------------+---------+----+---------+----------+---------+

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

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

SHOW COLUMNS列出了给定表的所有字段。请注意，如果看到的字段类型和你以前用 CREATE TABLE创建的字段类型不同的话，可能是MySQL有时候会在创建或修改表结构时修改字段类型。什么情况下MySQL会改变字段类型详情请看

从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是一样的。同时还可以通过命令 mysqlshow db_name tbl_name来列出所有的字段。

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 */

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的值来决定是否要把表名和字段名用引号引用起来。详情请看

SHOW CREATE VIEW view_name

根据给定的试图名显示一个完整的 CREATE VIEW语句。它是在MySQL 5.0.1中新增的。

mysql> SHOW CREATE VIEW v;

+-------+----------------------------------------------------+

| Table | Create Table|

+-------+----------------------------------------------------+

| v| CREATE VIEW `test`.`v` AS select 1 AS `a`,2 AS `b` |

+-------+----------------------------------------------------+

这个语法是在MySQL 5.0.1加上的。

SHOW DATABASES [LIKE 'pattern']

SHOW DATABASES列出了MySQL服务器上所有的数据库，通过 mysqlshow命令也可以得到同样的结果。从MySQL 4.0.2开始，如果没有全局的 mysqlshow权限的话，就只能列出那些有相关权限的数据库了。如果服务器启动时增加参数 --skip-show-database，并且你没有 SHOW DATABASES权限的话，就无法通过这个语句来列出数据库了。

SHOW [STORAGE] ENGINES

SHOW ENGINES显示了存储引擎相关的状态信息。这对检查是否支持某种存储引擎或者什么存储引擎是默认的非常有用。这个语句是在MySQL 4.1.2实现的。SHOW TABLE TYPES跟它一样，但是不推荐使用。

mysql> SHOW ENGINES\G

*************************** 1. row ***************************

Engine: MyISAM

Support: DEFAULT

Comment: Default engine as of MySQL 3.23 with great performance

*************************** 2. row ***************************

Engine: HEAP

Support: YES

Comment: Alias for MEMORY

*************************** 3. row ***************************

Engine: MEMORY

Support: YES

Comment: Hash based, stored in memory, useful for temporary tables

*************************** 4. row ***************************

Engine: MERGE

Support: YES

Comment: Collection of identical MyISAM tables

*************************** 5. row ***************************

Engine: MRG_MYISAM

Support: YES

Comment: Alias for MERGE

*************************** 6. row ***************************

Engine: ISAM

Support: NO

Comment: Obsolete storage engine, now replaced by MyISAM

*************************** 7. row ***************************

Engine: MRG_ISAM

Support: NO

Comment: Obsolete storage engine, now replaced by MERGE

*************************** 8. row ***************************

Engine: InnoDB

Support: YES

Comment: Supports transactions, row-level locking, and foreign keys

*************************** 9. row ***************************

Engine: INNOBASE

Support: YES

Comment: Alias for INNODB

*************************** 10. row ***************************

Engine: BDB

Support: YES

Comment: Supports transactions and page-level locking

*************************** 11. row ***************************

Engine: BERKELEYDB

Support: YES

Comment: Alias for BDB

*************************** 12. row ***************************

Engine: NDBCLUSTER

Support: YES

Comment: Clustered, fault-tolerant, memory-based tables

*************************** 13. row ***************************

Engine: NDB

Support: YES

Comment: Alias for NDBCLUSTER

*************************** 14. row ***************************

Engine: EXAMPLE

Support: YES

Comment: Example storage engine

*************************** 15. row ***************************

Engine: ARCHIVE

Support: YES

Comment: Archive storage engine

Support字段的值表明是否支持什么存储引擎和哪种存储引擎是默认的。例如，如果服务器启动时增加参数 --default-table-type=InnoDB，那么InnoDB这行的 Support字段的值就是 DEFAULT。

SHOW ERRORS [LIMIT [offset,] row_count]

SHOW COUNT(*) ERRORS

这个语句跟 SHOW WARNINGS类似，不同之处是 SHOW WARNINGS同时显示了错误，警告以及注意，而它却只显示错误信息。SHOW ERRORS是从MySQL 4.1.0开始有的。LIMIT分句和的 SELECT语法一样，详情请看SHOW COUNT(*) ERRORS语句显示了错误信息的总记录数。也可以通过取得变量 error_count的值来获得总错误记录数：

SHOW COUNT(*) ERRORS;

SELECT @@error_count;

SHOW GRANTS FOR user

该语句列出了某个MySQL帐户用 GRANT语句授权的全部权限。

mysql> SHOW GRANTS FOR 'root'@'localhost';

+---------------------------------------------------------------------+

| Grants for root@localhost|

+---------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |

+---------------------------------------------------------------------+

从MySQL 4.1.2开始，也可以使用以下几个语句之一来列出当前会话具备的权限：

SHOW GRANTS;

SHOW GRANTS FOR CURRENT_USER;

SHOW GRANTS FOR CURRENT_USER();

在MySQL 4.1.2以前，可以通过函数 CURRENT_USER() (MySQL 4.0.6新增的)的值来知道当前会话授权给哪个用户，并且把它的值用于 SHOW GRANTS语句中。详情请看SHOW GRANTS是在MySQL 3.23.4中新增的。

SHOW INDEX FROM tbl_name [FROM db_name]

SHOW INDEX很像ODBC的 SQLStatistics调用，它按照一定的格式返回数据表的索引信息。SHOW INDEX返回以下字段：

Table

表名。

Non_unique

0表示该索引不能有重复的记录，反之则反。

Key_name

索引名。

Seq_in_index

该字段在索引中的次序，从1开始。

Column_name

字段名。

Collation

字段在索引中是怎么排序的。在MySQL中，它的值可以是`A' (升序)或 NULL (无序)。

Cardinality

该索引中唯一值的数量，当执行 ANALYZE TABLE或 myisamchk -a. Cardinality时会更新它的值。 Cardinality(基数) 是用整数存储的基于统计的结果，因此如果是小表，那么它的结果可能未必精确。基数越大，则MySQL在做表连接时使用索引的几率越大。

Sub_part

它表示某个字段对多长的字符做索引。当整个字段都做索引了，那么它的值是 NULL。

Packed

表示键值是如何压缩的，NULL表示没有压缩。

Null

当字段包括 NULL的记录是 YES，它的值为，反之则是 ''。

Index_type

使用了哪种索引算法(有BTREE, FULLTEXT, HASH, RTREE)。

Comment

各种不同的备注。在MySQL 4.0.2以前，如果增加了 Index_type字段，则 Comment字段用户表示该索引是为 FULLTEXT索引。字段 Packed和 Comment是在MySQL 3.23.0中新增的。而 Null和 Index_type是在MySQL 4.0.2中新增的。在 tbl_nameFROM db_name语法中，还可以选择是否使用 db_name.tbl_name的格式。以下的这2个语句是一样的：

mysql> SHOW INDEX FROM mytable FROM mydb;

mysql> SHOW INDEX FROM mydb.mytable;

SHOW KEYS和 SHOW INDEX是一样的，也可以通过执行 mysqlshow -k db_name tbl_name命令来列出数据表的索引信息。

SHOW INNODB STATUS

它显示了 InnoDB存储引擎状态的其他的扩展信息。

SHOW [BDB] LOGS

SHOW LOGS显示了现存的日志文件的状态。它是在MySQL 3.23.29中实现的。现在它只显示了Berkeley DB类型的日志文件，因此它的另一个用法是SHOW LOGS(从MySQL 4.1.1开始)。SHOW LOGS返回了以下几个字段：

File

日志文件的完整路径。

Type

日志文件的类型(例如：BDB表示Berkeley DB类型的日志文件)。

Status

文件状态(FREE表示文件可以删除，IN USE表示在事务子系统中需要用到该日志文件)。

SHOW PRIVILEGES

SHOW PRIVILEGES列出了所有MySQL基本服务器所支持的权限。它是在MySQL 4.1.0中实现的。

mysql> SHOW PRIVILEGES\G

*************************** 1. row ***************************

Privilege: Select

Context: Tables

Comment: To retrieve rows from table

*************************** 2. row ***************************

Privilege: Insert

Context: Tables

Comment: To insert data into tables

*************************** 3. row ***************************

Privilege: Update

Context: Tables

Comment: To update existing rows

*************************** 4. row ***************************

Privilege: Delete

Context: Tables

Comment: To delete existing rows

*************************** 5. row ***************************

Privilege: Index

Context: Tables

Comment: To create or drop indexes

*************************** 6. row ***************************

Privilege: Alter

Context: Tables

Comment: To alter the table

*************************** 7. row ***************************

Privilege: Create

Context: Databases,Tables,Indexes

Comment: To create new databases and tables

*************************** 8. row ***************************

Privilege: Drop

Context: Databases,Tables

Comment: To drop databases and tables

*************************** 9. row ***************************

Privilege: Grant

Context: Databases,Tables

Comment: To give to other users those privileges you possess

*************************** 10. row ***************************

Privilege: References

Context: Databases,Tables

Comment: To have references on tables

*************************** 11. row ***************************

Privilege: Reload

Context: Server Admin

Comment: To reload or refresh tables, logs and privileges

*************************** 12. row ***************************

Privilege: Shutdown

Context: Server Admin

Comment: To shutdown the server

*************************** 13. row ***************************

Privilege: Process

Context: Server Admin

Comment: To view the plain text of currently executing queries

*************************** 14. row ***************************

Privilege: File

Context: File access on server

Comment: To read and write files on the server

SHOW [FULL] PROCESSLIST

SHOW PROCESSLIST显示了有哪些线程在运行。也可以执行 mysqladmin processlist命令来得到这些信息。如果有 SUPER权限，则可以看到全部的线程，否则，只能看到自己发起的线程(这是指，当前对应的MySQL帐户运行的线程)。详情请看FULL，则只能看到每个查询的前100个字符。

从MySQL 4.0.12起，结果中还会以的 host_name:client_port格式来显示通过TCP/IP方式连接过来的客户端的主机名，这就可以知道每个客户端都正在做什么。

这个语句在出现“too many connections”错误时想看看都正在执行什么查询非常有用。MySQL为拥有 SUPER权限的账户保留了一个额外的连接，这就保证让管理员总是可以连上检查系统状况(假定没有给每个系统账户都授予这个权限)。

SHOW PROCESSLIST结果中一些常见的状态如下：

Checking table

正在检查数据表(这是自动的)。

Closing tables

正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。

Connect Out

同步从服务器正在连接主服务器。

Copying to tmp table on disk

由于临时结果集大于 tmp_table_size，正在将临时表从内存存储转为磁盘存储以节省内存。

Creating tmp table

正在创建临时表以存放部分查询结果。

deleting from main table

服务器正在执行多表删除中的第一部分，刚删除第一个表。

deleting from reference tables

服务器正在执行多表删除中的第二部分，正在删除其他表的记录。

Flushing tables

正在执行 FLUSH TABLES，等待其他线程关闭数据表。

Killed

发送了一个 kill请求给某线程，那么这个线程将会检查 kill标志位，同时会放弃下一个 kill请求。MySQL会在每次的主循环中检查 kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么 kill请求会在锁释放时马上生效。

Locked

被其他查询锁住了。

Sending data

正在处理 SELECT查询的记录，同时正在把结果发送给客户端。

Sorting for group

正在为 GROUP BY做排序。

Sorting for order

正在为 ORDER BY做排序。

Opening tables

这个过程应该会很快，除非受到其他因素的干扰。例如，在执 ALTER TABLE或 LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。

Removing duplicates

正在执行一个 SELECT DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后把结果发送给客户端。

Reopen table

获得对一个表的锁，但是被通知到得到锁之后该表结构会发生变化。因此就先释放锁，关闭表，重新打开它

Repair by sorting

修复指令正在用排序算法创建索引。

Repair with keycache

修复指令正在利用索引缓存一个个地创建新索引。它会比 Repair by sorting慢很多。

Searching rows for update

正在将符合条件的记录找出来以备更新。它必须在 UPDATE要修改相关的记录之前就完成了。

Sleeping

正在等待客户端发送新请求.

System lock

正在等待取得一个外部系统锁。如果当前没有需要运行多个 mysqld服务器同时来请求同一个表，那么可以通过增加 --skip-external-locking参数来禁止外部系统锁。

Upgrading lock

INSERT DELAYED处理程序正在尝试取得一个锁表以插入新记录。

Updating

正在搜索需要更新的记录，并正在更新。

User Lock

正在等待 GET_LOCK()。

Waiting for tables

该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或 OPTIMIZE TABLE。

waiting for handler insert

INSERT DELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。

大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。

还有其他的状态没在上面中列出来，不过它们大部分只是在查看服务器是否产生错误了时才用得着。

SHOW STATUS [LIKE 'pattern']

SHOW STATUS显示了各种服务器状态信息。也可以通过运行 mysqladmin extended-status命令来得到结果。这里有部分输出结果，某些变量和它对应的值可能跟你的系统不大一样。各种变量所代表的意义详情请看

mysql> SHOW STATUS;

+--------------------------+------------+

| Variable_name| Value|

+--------------------------+------------+

| Aborted_clients| 0|

| Aborted_connects| 0|

| Bytes_received| 155372598 |

| Bytes_sent| 1176560426 |

| Connections| 30023|

| Created_tmp_disk_tables| 0|

| Created_tmp_tables| 8340|

| Created_tmp_files| 60|

...

| Open_tables| 1|

| Open_files| 2|

| Open_streams| 0|

| Opened_tables| 44600|

| Questions| 2026873|

...

| Table_locks_immediate| 1920382|

| Table_locks_waited| 0|

| Threads_cached| 0|

| Threads_created| 30022|

| Threads_connected| 1|

| Threads_running| 1|

| Uptime| 80380|

+--------------------------+------------+

可以通过 LIKE分句，就可以值显示匹配的变量及其值：

mysql> SHOW STATUS LIKE 'Key%';

+--------------------+----------+

| Variable_name| Value|

+--------------------+----------+

| Key_blocks_used| 14955|

| Key_read_requests| 96854827 |

| Key_reads| 162040|

| Key_write_requests | 7589728|

| Key_writes| 3813196|

+--------------------+----------+

SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']

SHOW TABLE STATUS(在MySQL 3.23新增的)跟 SHOW TABLE类似，它还显示每个表的许多相关信息。也可以通过运行 mysqlshow --status db_name命令来显示同样的结果。

SHOW TABLE STATUS返回了以下几个字段的信息：

Name

表名。

Engine

数据表的存储引擎类型，在MySQL 4.1.2以前，这个值被标志为 Type。详情请看

Version

数据表的 `.frm'文件版本号。

Row_format

记录存储格式(Fixed—固定的, Dynamic—动态的,或 Compressed—压缩的)。

Rows

记录数。在某些存储引擎中，例如MyISAM和 ISAM他们存储了精确的记录数。不过其他存储引擎中，例如 InnoDB，它可能只是近似值。

Avg_row_length

记录的平均长度。

Data_length

数据文件长度。

Max_data_length

数据文件的最大长度。对固定格式存储记录的表来说，它是表的最大记录数。对动态存储记录的表来说，它是数据表存储的最大字节数，给出了数据指针使用的大小。

Index_length

索引文件的长度。

Data_free

已经分配还未使用的字节数。

Auto_increment

下一个 AUTO_INCREMENT的值。

Create_time

表的创建时间。

Update_time

表最后一次更新时间。

Check_time

表最后一次检查时间。

Collation

表的字符集和校正字符集(在中MySQL4.1.1新增的)。

Checksum

实时的校验和值(如果有的话) (在中MySQL4.1.1新增的)。

Create_options

额外留给 CREATE TABLE的选项。

Comment

创建表时的备注(或者一些MySQL无法存取改表的相关信息)。

表注释一栏，InnoDB类型的表会报告它所属的表剩余表空间。对一个使用共享表空间的表来说，它是指共享表空间的剩余空间。如果使用多表空间并且该表有自己的表空间，那么剩余空间就是全部属于这个表的。

对 MEMORY (HEAP)(内存)表来说，Data_length, Max_data_length,和 Index_length的值接近内存实际分配结果。分配算法预留了大量内存空间以减少重复分配内存的操作。

SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern']

SHOW TABLES列出了给定数据库中的非临时(non-TEMPORARY)表。也可以通过命令 mysqlshow db_name来列出这些表。

在MySQL 5.0.1以前，SHOW TABLES的结果中只有一个字段显示表名。从MySQL 5.0.1开始，SHOW TABLES还列出了数据库中的视图，还有第二个字段。第二字段的值是表的 BASE TABLE(基表)或者视图名。

请注意，如果没有表的相应权限，那么在 SHOW TABLES或mysqlshow db_name结果中就不会列出该表。

SHOW OPEN TABLES列出了所有当前正被打开的表。详情请看Comment字段告诉我们该表被 cached和 in_use的时间。从MySQL 3.23.33开始，可以使用 OPEN关键字。

SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern']

SHOW VARIABLES显示了一些MySQL的系统变量值。这些信息也可以通过运行命令 mysqladmin variables来得到。

GLOBAL和 SESSION可选项是从MySQL 4.0.3开始可以用的。如果是 SESSION，就会得到每次新连接中使用的变量值。如果是

SESSION，则得到当前连接中变量的值。如果你不加任何选选项，那么 SESSION就是默认的。LOCAL和 SESSION一样。

如果变量的默认值不大合适，则可以在 mysqld启动时通过命令行增加相关选项或者通过 SET语句来做到。详情请看

以下列出了部分结果，这些结果值可能与您的系统的值不一样。每个变量的值对应的意义详情请看

mysql> SHOW VARIABLES;

+---------------------------------+------------------------------+

| Variable_name| Value|

+---------------------------------+------------------------------|

| back_log| 50|

| basedir| /usr/local/mysql|

| bdb_cache_size| 8388572|

| bdb_log_buffer_size| 32768|

| bdb_home| /usr/local/mysql|

...

| max_connections| 100|

| max_connect_errors| 10|

| max_delayed_threads| 20|

| max_error_count| 64|

| max_heap_table_size| 16777216|

| max_join_size| 4294967295|

| max_relay_log_size| 0|

| max_sort_length| 1024|

...

| timezone| EEST|

| tmp_table_size| 33554432|

| tmpdir| /tmp/:/mnt/hd2/tmp/|

| version| 4.0.4-beta|

| wait_timeout| 28800|

+---------------------------------+------------------------------+

可以使用 LIKE分句列出匹配的变量：

mysql> SHOW VARIABLES LIKE 'have%';

+--------------------+----------+

| Variable_name| Value|

+--------------------+----------+

| have_bdb| YES|

| have_innodb| YES|

| have_isam| YES|

| have_raid| NO|

| have_symlink| DISABLED |

| have_openssl| YES|

| have_query_cache| YES|

+--------------------+----------+

SHOW WARNINGS [LIMIT [offset,] row_count]

SHOW COUNT(*) WARNINGS

SHOW WARNINGS显示了最后一个查询语句产生的错误、警告、提示等信息，如果它没有产生任何信息，则不显示任何信息。这个语句是在MySQL 4.1.0实现的。它的一个相关语句是 SHOW ERRORS，不过它只显示错误信息，详情请看

随着执行语句的不同每次显示的信息都会产生变化。

SHOW COUNT(*) WARNINGS语句显示了错误、警告、提示信息的总记录数。也可以通过取得变量 warning_count的值来获得总错误记录数：

SHOW COUNT(*) WARNINGS;

SELECT @@warning_count;

warning_count的值可能会比 SHOW WARNINGS显示的结果记录数大，因为系统变量 max_error_count被设置的比较小，因此没有把所有的信息都存下来。本章节后面演示了发生这种情况的例子。

LIMIT分句的用法和在 SELECT语句一样。详情请看

MySQL服务器把最后一次查询中产生的错误、警告、提示信息总数返回了。如果使用C API，这个值可以通过调用 mysql_warning_count()得到。详情请看

注意，警告信息的框架在MySQL 4.1.0中增加了，在这个时候还有很多语句不产生警告信息。在4.1.1中，这种情况得到了改善，很多语句都产生了警告信息，例如：LOAD DATA INFILE及 INSERT, UPDATE, CREATE TABLE,和 ALTER TABLE等。

以下 DROP TABLE语句就产生了一个提示信息：

mysql> DROP TABLE IF EXISTS no_such_table;

mysql> SHOW WARNINGS;

+-------+------+-------------------------------+

| Level | Code | Message|

+-------+------+-------------------------------+

| Note| 1051 | Unknown table 'no_such_table' |

+-------+------+-------------------------------+

以下是一个 CREATE TABLE和 INSERT语句产生的语法警告和转换警告信息：

mysql> CREATE TABLE t1 (a TINYINT NOT NULL, b CHAR(4)) TYPE=MyISAM default charset utf8 ;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G

*************************** 1. row ***************************

Level: Warning

Code: 1287

Message: 'TYPE=storage_engine' is deprecated, use

'ENGINE=storage_engine' instead

1 row in set (0.00 sec)

mysql> INSERT INTO t1 VALUES(10,'mysql'),(NULL,'test'),

-> (300,'open source');

Query OK, 3 rows affected, 4 warnings (0.01 sec)

Records: 3Duplicates: 0Warnings: 4

mysql> SHOW WARNINGS\G

*************************** 1. row ***************************

Level: Warning

Code: 1265

Message: Data truncated for column 'b' at row 1

*************************** 2. row ***************************

Level: Warning

Code: 1263

Message: Data truncated, NULL supplied to NOT NULL column 'a' at row 2

*************************** 3. row ***************************

Level: Warning

Code: 1264

Message: Data truncated, out of range for column 'a' at row 3

*************************** 4. row ***************************

Level: Warning

Code: 1265

Message: Data truncated for column 'b' at row 3

4 rows in set (0.00 sec)

错误、警告、提示信息的最大存储数量是由系统变量 max_error_count控制的。它的默认值是64，通过修改 max_error_count的值就可以改变最大存储数量。以下例子中，ALTER TABLE语句产生了3个警告信息，不过只存储了1条，因为 max_error_count的值为 1：

mysql> SHOW VARIABLES LIKE 'max_error_count';

+-----------------+-------+

| Variable_name| Value |

+-----------------+-------+

| max_error_count | 64|

+-----------------+-------+

1 row in set (0.00 sec)

mysql> SET max_error_count=1;

Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE t1 MODIFY b CHAR;

Query OK, 3 rows affected, 3 warnings (0.00 sec)

Records: 3Duplicates: 0Warnings: 3

mysql> SELECT @@warning_count;

+-----------------+

| @@warning_count |

+-----------------+

|3 |

+-----------------+

1 row in set (0.01 sec)

mysql> SHOW WARNINGS;

+---------+------+----------------------------------------+

| Level| Code | Message|

+---------+------+----------------------------------------+

| Warning | 1263 | Data truncated for column 'b' at row 1 |

+---------+------+----------------------------------------+

1 row in set (0.00 sec)

想要禁止警告信息，只要将 max_error_count设置为 0。在这种情况下，warning_count仍然是产生的警告信息总数，但不存储任何警告信息。

風来坊Furaibo

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql set 语法_MYSQL SET 和 SHOW 语法

SET可以用于设置各种变量或者选项。SHOW可以以各种形式描述数据库系统的信息，诸如数据表，字段，服务器状态等等。大致有以下这些方法：SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern']SHOW CREATE DATABASE db_nameSHOW CREATE TABLE tbl_nameSHOW DATABASES...
复制链接

扫一扫