MySQL Administrator’s Bible- P15

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

107
lượt xem 11
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

MySQL Administrator’s Bible- P15: Welcome to the MySQL Administrator’s Bible. Like all books in the Bible series, you can expect to find both hands-on tutorials and real-world practical application information, as well as reference and background information that provide a context for what you are learning.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: MySQL Administrator’s Bible- P15

The MySQL Data Dictionary M ySQL stores information about the data in the databases; this is called metadata. Much of this information is stored in the IN THIS CHAPTER INFORMATION_SCHEMA database, following the SQL 2003 standard. Examining the Object catalog Tables in the INFORMATION_SCHEMA database are read-only, in-memory, System information and show data from various sources. Managing permissions SHOW CREATE TABLE will show the tables as TEMPORARY, because Developing custom metadata they reside in memory and do not persist between mysqld restarts. INFORMATION_SCHEMA tables are called system views and they may be of different storage engine types. At the time of this writing all the system views are either the MEMORY, MyISAM, or Maria storage engine. Regular SQL statements can be used to query them, though they have some special properties that other views do not have: ■ mysqldump will not export any information (data, schema) from INFORMATION_SCHEMA system views ■ There is no data directory for the INFORMATION_SCHEMA database ■ There is no .frm ﬁle associated with the INFORMATION_SCHEMA views. The deﬁnitions are hard-coded into the database. The table deﬁnitions for the data dictionary are hard-coded into the source code, and loaded when mysqld starts. Unlike other databases, there is no directory in the datadir for the INFORMATION_SCHEMA database. All users have permission to see the INFORMATION_SCHEMA database; however, they can only see the objects they have permission to see. For example, table details in the TABLES system view are limited to the tables that the user has permission to see. 667
Part IV Extending Your Skills Some of the metadata provided by the INFORMATION_SCHEMA database is also provided by various SHOW commands (see Chapter 4). The INFORMATION_SCHEMA database is a more complete data dictionary than using SHOW commands. Also, standard SQL statements can be used to query the system views to retrieve metadata. The INFORMATION_SCHEMA database contains more than 40 system views. They can be infor- mally categorized as: ■ Object catalog (databases, tables, columns, and so on) ■ System information (variables, statistics, available options) ■ Permissions ■ Storage engine-speciﬁc metadata Querying metadata is a powerful tool for a database administrator, answering simple questions such as ‘‘how many tables of each storage engine type exist?’’ and ‘‘which tables have columns using the DECIMAL type?’’ and ‘‘how many foreign key constraints exist?’’ and even ‘‘how much space does a certain group of three tables use?’’ Querying metadata provides a way to retrieve information about the system that can be used to track and tune performance. Any tool that per- forms queries can retrieve metadata by querying the INFORMATION_SCHEMA database, exactly the same way it queries any other database. Object Catalog The INFORMATION_SCHEMA database contains system views with metadata about objects such as databases, tables, views, columns, indexes, partitions, stored routines, triggers, and events. SCHEMATA ‘‘Schema’’ is another name for a database, and ‘‘schemata’’ is the plural of schema. The SCHEMATA system view in the INFORMATION_SCHEMA database provides information about all the databases, including the mysql system database and the INFORMATION_SCHEMA database itself. The ﬁelds in the SCHEMATA system view are: ■ CATALOG_NAME — Provided for standards compliance. However, because MySQL does not have catalogs, this value is always NULL. ■ SCHEMA_NAME — The name of the database, such as sakila. ■ DEFAULT_CHARACTER_SET_NAME — The default character set of the database. If no default character set is assigned by a CREATE DATABASE or ALTER DATABASE command, the default character set for the system is stored. Thus, the DEFAULT_CHARACTER_SET_ NAME ﬁeld always has a non-NULL value, and defaults to the character set of the system at the time of database creation. 668
The MySQL Data Dictionary 21 ■ DEFAULT_COLLATION_NAME — The default collation of the database. If no default collation is assigned by a CREATE DATABASE or ALTER DATABASE command, the default collation for the system is stored. Thus, the DEFAULT_CHARACTER_SET_NAME ﬁeld always has a non-NULL value, and defaults to the collation of the system at the time of database creation. ■ SQL_PATH — Provided for standards compliance, this ﬁeld is usually used to ﬁnd ﬁles related to the database. However, MySQL does not support this ﬁeld, so it is always NULL. The SHOW DATABASES command is a shorter way to ﬁnd the names of existing databases than running SELECT SCHEMA_NAME FROM SCHEMATA. To show a subset of all databases, it is easier to use the SCHEMATA system view. The SHOW DATABASES command returns a result set where the ﬁeld name is Database. Because Database is a reserved word, in order to use the WHERE extension to SHOW DATABASES, the Database ﬁeld must be quoted: mysql> SHOW DATABASES WHERE Database NOT IN (’mysql’,’information_ schema’); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’NOT IN (’mysql’,’information_schema’)’ at line 1 mysql> SHOW DATABASES WHERE `Database` NOT IN (’mysql’,’information_ schema’); +----------+ | Database | +----------+ | sakila | | test | +----------+ 2 rows in set (0.02 sec) See Chapter 4 for more information about using the backtick (`) to quote identiﬁers. The ﬁeld name in the SCHEMATA system view is SCHEMA_NAME, which is not a reserved word, and does not need to be escaped: mysql> USE INFORMATION_SCHEMA; Database changed mysql> SELECT SCHEMA_NAME FROM SCHEMATA WHERE SCHEMA_NAME NOT IN (’mysql’,’information_schema’); +-------------+ | SCHEMA_NAME | +-------------+ | sakila | | test | +-------------+ 2 rows in set (0.02 sec) The SHOW DATABASES command also accepts the LIKE extension. The SHOW SCHEMAS command behaves the same way as the SHOW DATABASES command, and outputs the same information — SCHEMAS is an alias for DATABASES. 669
Part IV Extending Your Skills The SHOW CREATE DATABASE command returns two ﬁelds: Database, which is equivalent to SCHEMA_NAME, and Create Database, which does not have an exact equivalent but contains the value of DEFAULT_CHARACTER_SET_NAME. SCHEMA is an alias for DATABASE, so SHOW CREATE SCHEMA returns the same information as SHOW CREATE DATABASE does. TABLES Metadata about non-temporary tables is available in the TABLES system view. The ﬁelds in the TABLES system view are: ■ TABLE_CATALOG — Provided for standards compliance. However, because MySQL does not have catalogs, this value is always NULL. ■ TABLE_SCHEMA — The name of the database, such as sakila. ■ TABLE_NAME — The name of the table. ■ TABLE_TYPE — Whether the table is a base table, view, or system view. Only the INFOR- MATION_SCHEMA views are system views. ■ ENGINE — The storage engine of the table, such as InnoDB. To ﬁnd out which tables, if any, in the INFORMATION_SCHEMA database do not use the MEMORY storage engine: mysql> SELECT TABLE_NAME,ENGINE FROM TABLES WHERE TABLE_ SCHEMA=’INFORMATION_SCHEMA’ AND ENGINE!=’MEMORY’; +-------------+--------+ | TABLE_NAME | ENGINE | +-------------+--------+ | COLUMNS | MARIA | | EVENTS | MARIA | | PARAMETERS | MARIA | | PARTITIONS | MARIA | | PLUGINS | MARIA | | PROCESSLIST | MARIA | | ROUTINES | MARIA | | TRIGGERS | MARIA | | VIEWS | MARIA | +-------------+--------+ 9 rows in set (0.56 sec) ■ From this query we see that most system views in the INFORMATION_SCHEMA database are the MEMORY storage engine, but there are some that use the Maria storage engine. ■ VERSION — The version of the .frm ﬁle, currently 10. The VERSION is NULL for table objects that do not have .frm ﬁles, such as views. The exception to this rule is system views, which have a VERSION of 10 — even though there are no .frm ﬁles, system views have hard-coded deﬁnitions, and thus have versions. ■ ROW_FORMAT — Different storage engines allow the row storage to vary. Fixed-width rows are a ﬁxed size, which minimizes fragmentation. Dynamic rows are a variable size, which are good for variable-length data, such as VARCHAR, TEXT, and BLOB. InnoDB has 670
The MySQL Data Dictionary 21 a compact row format by default, which eliminates some redundant data. When an Inn- oDB table has a redundant format, there is less CPU work needed at the cost of additional storage space. MyISAM has a compressed format, obtained by packing the data with the myisampack tool. See Chapter 11 for more details on myisampack. ■ TABLE_ROWS — The number of rows this table contains. This value may be an estimate, depending on the storage engine. The value is NULL for views and system views (INFORMATION_SCHEMA tables). ■ AVG_ROW_LENGTH — The average size in bytes of rows this table contains. This value may be an estimate, depending on the storage engine. The value is NULL for views, but has a value for system views. If there are no rows, the value will be 0. ■ DATA_LENGTH — The size in bytes of rows this table contains. This value may be an esti- mate, depending on the storage engine. The value is NULL for views and 0 for MEMORY tables. System views that are not MEMORY tables have a value for DATA_LENGTH. ■ MAX_DATA_LENGTH — The maximum size in bytes that this table may contain. The value is NULL for views, because there is no data stored. The value is 0 for storage engines that do not populate this ﬁeld, such as Falcon and CSV. ■ INDEX_LENGTH — The size in bytes of the indexes for this table. This value may be an estimate, depending on the storage engine. The value is NULL for views and 0 for MEM- ORY tables. System views that are not MEMORY tables have a value for DATA_LENGTH. ■ DATA_FREE — The size in bytes of the free space allocated for this table, and still avail- able. This value may be an estimate, depending on the storage engine. The value is NULL for views and 0 for system views. Many tables have a DATA_FREE value of 0 because there is not space allocated for them, though there may be plenty of free space available to them. For example, CSV tables simply use available disk space, without needing MySQL to allocate space for rows. In some storage engines such as MyISAM, this might indicate fragmentation and that the table needs to be rebuilt with an OPTIMIZE command. See Chapter 4 for more information about OPTIMIZE. ■ AUTO_INCREMENT — The next AUTO_INCREMENT value to be used. If the maximum AUTO_INCREMENT value for a table is 100, the value of AUTO_INCREMENT is 101. If a table has an AUTO_INCREMENT value and no rows have ever been stored in the table, the value of AUTO_INCREMENT is 1. ■ CREATE_TIME — The DATETIME the table was created. The value is NULL for views and MEMORY tables. System views that use storage engines other than MEMORY have a proper DATETIME value. FEDERATED tables have a value of NULL. ■ UPDATE_TIME — The most recent DATETIME that an ALTER TABLE was performed on the table. The value is NULL for views, CSV, and MEMORY tables. MyISAM, Archive, and Maria tables that have never had ALTER TABLE performed on them have an UPDATE_TIME equivalent to their CREATE_TIME. InnoDB and Falcon tables that have never had ALTER TABLE performed on them have a NULL value. System views that use storage engines other than MEMORY have a proper DATETIME value. FEDERATED tables have a value of NULL. 671
Part IV Extending Your Skills ■ CHECK_TIME — The most recent DATETIME the table was checked with CHECK TABLE. The value is NULL for views, system views, and tables that have never been checked or do not support the check function. ■ TABLE_COLLATION — The character set and collation of the table, for example utf8_bin utf8_general_ci, or latin1_swedish_ci. The value is NULL for views. If no default character set and collation is assigned by a CREATE TABLE or ALTER TABLE command, the default character set and collation are stored. Thus, this ﬁeld always has a non-NULL value for base tables and system views. ■ CHECKSUM — Live checksums can be maintained for MyISAM tables (see Chapter 11). If this table is a MyISAM table with CHECKSUM=1, the live checksum value is displayed. For all other tables, the value is NULL. ■ CREATE_OPTIONS — CREATE TABLE has many different options. The options that are not shown in other ﬁelds (such as TABLE_COLLATION) are shown in this ﬁeld, separated by a space. Sample values are partitioned and max_rows=10000 checksum=1. If there are no relevant options to CREATE TABLE, the value is the empty string (’’). The value is NULL for views. ■ TABLE_COMMENT — The COMMENT option to CREATE TABLE and ALTER TABLE can be used to provide information about a table. If there was no comment speciﬁed, the value is the empty string (‘’). The value is VIEW for views. Most of the tables in the mysql system database have comments: mysql> SELECT TABLE_NAME, TABLE_COMMENT FROM TABLES WHERE TABLE_SCHEMA=’mysql’\G *************************** 1. row *************************** TABLE_NAME: backup_history TABLE_COMMENT: *************************** 2. row *************************** TABLE_NAME: backup_progress TABLE_COMMENT: *************************** 3. row *************************** TABLE_NAME: columns_priv TABLE_COMMENT: Column privileges *************************** 4. row *************************** TABLE_NAME: db TABLE_COMMENT: Database privileges *************************** 5. row *************************** TABLE_NAME: event TABLE_COMMENT: Events *************************** 6. row *************************** TABLE_NAME: func TABLE_COMMENT: User defined functions *************************** 7. row *************************** TABLE_NAME: general_log TABLE_COMMENT: General log *************************** 8. row *************************** TABLE_NAME: help_category 672
The MySQL Data Dictionary 21 TABLE_COMMENT: help categories *************************** 9. row *************************** TABLE_NAME: help_keyword TABLE_COMMENT: help keywords *************************** 10. row *************************** TABLE_NAME: help_relation TABLE_COMMENT: keyword-topic relation *************************** 11. row *************************** TABLE_NAME: help_topic TABLE_COMMENT: help topics *************************** 12. row *************************** TABLE_NAME: host TABLE_COMMENT: Host privileges; Merged with database privileges *************************** 13. row *************************** TABLE_NAME: ndb_binlog_index TABLE_COMMENT: *************************** 14. row *************************** TABLE_NAME: plugin TABLE_COMMENT: MySQL plugins *************************** 15. row *************************** TABLE_NAME: proc TABLE_COMMENT: Stored Procedures *************************** 16. row *************************** TABLE_NAME: procs_priv TABLE_COMMENT: Procedure privileges *************************** 17. row *************************** TABLE_NAME: servers TABLE_COMMENT: MySQL Foreign Servers table *************************** 18. row *************************** TABLE_NAME: slow_log TABLE_COMMENT: Slow log *************************** 19. row *************************** TABLE_NAME: tables_priv TABLE_COMMENT: Table privileges *************************** 20. row *************************** TABLE_NAME: time_zone TABLE_COMMENT: Time zones *************************** 21. row *************************** TABLE_NAME: time_zone_leap_second TABLE_COMMENT: Leap seconds information for time zones *************************** 22. row *************************** TABLE_NAME: time_zone_name TABLE_COMMENT: Time zone names *************************** 23. row *************************** TABLE_NAME: time_zone_transition TABLE_COMMENT: Time zone transitions *************************** 24. row *************************** TABLE_NAME: time_zone_transition_type TABLE_COMMENT: Time zone transition types 673
Part IV Extending Your Skills *************************** 25. row *************************** TABLE_NAME: user TABLE_COMMENT: Users and global privileges 25 rows in set (0.00 sec) There are a few SHOW commands that show table information. SHOW TABLES returns one ﬁeld, the equivalent of TABLE_NAME. SHOW FULL TABLES adds another ﬁeld, Table_type, which is the equivalent of TABLE_TYPE. The SHOW CREATE TABLE command returns two ﬁelds: Table, which is the equivalent of TABLE_NAME, and Create Table, which is the full CREATE TABLE statement. There is no equivalent in the TABLES system view, though it includes the information in ENGINE, AUTO_INCREMENT, CREATE_OPTIONS, and TABLE_COMMENT. SHOW TABLE STATUS returns many ﬁelds: ■ Name — Equivalent to TABLE_NAME. ■ Engine, Version, and Row_format — Equivalent to ENGINE, VERSION, and ROW_FORMAT. ■ Rows — Equivalent to TABLE_ROWS. ■ Avg_row_length, Data_length, Max_data_length, Index_length, Data_free, Auto_increment, Create_time, Update_time, and Check_time — Equivalent to AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT, CREATE_TIME, UPDATE_TIME, and CHECK_TIME. ■ Collation — Equivalent to TABLE_COLLATION. ■ Checksum, Create_options — Equivalent to CHECKSUM and CREATE_OPTIONS. ■ Comment — Equivalent to TABLE_COMMENT. VIEWS The TABLES system view includes rows for views. However, many of the ﬁelds in TABLES are NULL for views, and some features speciﬁc to views are not encompassed in the TABLES system view. So MySQL provides the VIEWS system view, with the following ﬁelds: ■ TABLE_CATALOG — Provided for standards compliance. However, because MySQL does not have catalogs, this value is always NULL. ■ TABLE_SCHEMA — The name of the database, such as sakila. ■ TABLE_NAME — The name of the view. ■ VIEW_DEFINITION — The SELECT statement that deﬁnes the view. If the current user is not the deﬁner, the value will be blank, even if the current user has permissions to see the view deﬁnition: mysql> SELECT DEFINER,VIEW_DEFINITION,CURRENT_USER() -> FROM VIEWS -> WHERE TABLE_NAME=’staff_list’; 674
The MySQL Data Dictionary 21 +----------------+-----------------+----------------+ | DEFINER | VIEW_DEFINITION | CURRENT_USER() | +----------------+-----------------+----------------+ | root@localhost | | root@127.0.0.1 | +----------------+-----------------+----------------+ 1 row in set (0.03 sec) mysql> SHOW GRANTS; +-------------------------------------------------------------+ | Grants for root@127.0.0.1 | +-------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO ’root’@’127.0.0.1’ WITH GRANT| | OPTION | +-------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW CREATE VIEW sakila.staff_list\G *************************** 1. row *************************** View: staff_list Create View: CREATE ALGORITHM=UNDEFINED DEFINER=root@ localhost SQL SECURITY DEFINER VIEW sakila.staff_list AS select s.staff_id AS ID,concat(s.first_name,_utf8’ ’,s.last_name) AS name, a.address AS address,a.postal_code AS zip code,a.phone AS phone, sakila.city.city AS city,sakila.country.country AS country,s. store_id AS SID from (((sakila.staff s join sakila.address a on((s.address_id = a.address_id))) join sakila.city on((a.city_id = sakila.city.city_id))) join sakila.country on((sakila.city. country_id = sakila.country.country_id))) character_set_client: utf8mb3 collation_connection: utf8mb3_general_ci 1 row in set (0.00 sec) ■ In this example, the user root@127.0.0.1 saw a blank view deﬁnition for the staff_list view, because the DEFINER is root@localhost. ■ CHECK_OPTION — This value is NONE if the view deﬁnition has no WITH CHECK OPTION clause; CASCADED if the view deﬁnition contains WITH [CASCADED] CHECK OPTION, and LOCAL if the view deﬁnition contains WITH LOCAL CHECK OPTION. ■ IS_UPDATABLE — YES if the view is updatable, NO if the view is not updatable. See Chapter 8, subsection ‘‘Updatable Views,’’ for more information on updatable views. ■ DEFINER — The view deﬁner, in the MySQL user@host format. ■ SECURITY_TYPE — DEFINER if the view deﬁnition was speciﬁed with the SQL SECURITY DEFINER option or did not contain an SQL SECURITY option. The value is INVOKER if the view deﬁnition was speciﬁed with the SQL SECURITY INVOKER option. ■ CHARACTER_SET_CLIENT — Stores the environmental character set as it was when the view was created. 675
Part IV Extending Your Skills ■ COLLATION_CONNECTION — Stores the environmental collation as it was when the view was created. SHOW CREATE VIEW is the SHOW command that shares the most information with the VIEWS sys- tem view. The ﬁelds of SHOW CREATE VIEW are: ■ View — Equivalent to TABLE_NAME. ■ Create View — No exact equivalent. This is the full CREATE VIEW statement, and has elements from TABLE_NAME, VIEW_DEFINITION, CHECK_OPTION, IS_UPDATABLE, DEFINER, and SECURITY_TYPE. ■ character_set_client — Equivalent to CHARACTER_SET_CLIENT. ■ collation_connection — Equivalent to COLLATION_CONNECTION. COLUMNS The COLUMNS system view contains information about table ﬁelds. This system view contains information about the ﬁelds from every table, view, and system view. ■ TABLE_CATALOG — Provided for standards compliance. However, because MySQL does not have catalogs, this value is always NULL. ■ TABLE_SCHEMA — The name of the database, such as sakila. ■ TABLE_NAME — The name of the table. ■ COLUMN_NAME — The name of the ﬁeld. ■ ORDINAL_POSITION — The number representing the order of the ﬁeld. The ﬁrst ﬁeld has a value of 1, the third ﬁeld has a value of 3, and so on. The value is never NULL. ■ COLUMN_DEFAULT — The default value of the ﬁeld. If the default is not speciﬁed or speci- ﬁed as NULL, the value is NULL. ■ IS_NULLABLE — Whether or not the ﬁeld is allowed to be null. If the ﬁeld is speciﬁed as NOT NULL, the value is NO. Otherwise, the value is YES. Note that it is possible to have a table where the value of IS_NULLABLE is NO and the COLUMN_DEFAULT is NULL: mysql> USE test; Database changed mysql> CREATE TABLE paradox (numfield INT NOT NULL); Query OK, 0 rows affected (0.11 sec) mysql> SELECT IS_NULLABLE,COLUMN_DEFAULT -> FROM INFORMATION_SCHEMA.COLUMNS -> WHERE TABLE_NAME=’paradox’ AND TABLE_SCHEMA=’test’; +-------------+----------------+ | IS_NULLABLE | COLUMN_DEFAULT | +-------------+----------------+ | NO | NULL | +-------------+----------------+ 1 row in set (0.00 sec) 676
The MySQL Data Dictionary 21 ■ DATA_TYPE — The data type of the ﬁeld, such as INT, CHAR, or ENUM. ■ CHARACTER_MAXIMUM_LENGTH — The maximum number of characters allowed by the ﬁeld deﬁnition. For example, a ﬁeld deﬁned as VARCHAR(64) has a CHARAC- TER_MAXIMUM_LENGTH of 64. This is only valid for string data, which includes CHAR, VARCHAR, TEXT, BLOB, SET, and ENUM types. ■ CHARACTER_OCTET_LENGTH — Maximum octet (byte) size allowed by the ﬁeld deﬁni- tion. CHARACTER_OCTET_LENGTH is the same as CHARACTER_MAXIMUM_LENGTH except when multi-byte character sets are used (such as utf8). ■ NUMERIC_PRECISION — Mathematically, precision is the number of digits used to deﬁne a number, for example 10 for an INT and 5 for a SMALLINT. This is only valid for numeric data types, including INT, DECIMAL, and FLOAT. For all other data types, the value is NULL. ■ NUMERIC_SCALE — This is only valid for numeric data types, including INT, DECIMAL, and FLOAT. For all other data types, the value is NULL. This value is equivalent to the number of digits after the decimal point, which is 0 for data types that are integers. ■ CHARACTER_SET_NAME — The default character set for this column. This is only valid for non-binary string data, which includes CHAR, VARCHAR, TEXT, SET, and ENUM types. ■ COLLATION_NAME — The default collation for this column. This is only valid for non-binary string data, which includes CHAR, VARCHAR, TEXT, SET, and ENUM types. Sample values are utf8_general_ci, latin1_bin, and latin1_swedish_ci. ■ COLUMN_TYPE — The entire data type part of the column deﬁnition, without any NULL or DEFAULT deﬁners. For example, varchar(64), bigint(21) unsigned, enum(’N’,’Y’), or year(4). ■ COLUMN_KEY — If the column is not part of an index, this ﬁeld contains the empty string (‘’). Otherwise, the value is the type of index the columns is a part of: ■ PRI — Primary key ■ UNI — Unique, non-primary key ■ MUL — Non-unique key ■ EXTRA — This ﬁeld stores extra information about the column that does not have a place in another ﬁeld. If there is no such extra information, the value is the empty string (‘’). Example values are on update CURRENT_TIMESTAMP and auto_increment. ■ PRIVILEGES — The privileges the querying user has for use with this column. If the user has no privileges for a column, there is no row returned for that column. Therefore, the ﬁeld always contains at least the select privilege. Privileges are separated by a comma, for example select,insert,update. ■ COLUMN_COMMENT — The COMMENT option to a column deﬁnition within CREATE TABLE and ALTER TABLE can be used to provide information about a column. If there was no comment speciﬁed, the value is the empty string (‘’). 677
Part IV Extending Your Skills ■ STORAGE — Indicates whether the column is stored in memory or on disk. These only apply to columns in NDB tables. The value for all other tables is Default. ■ FORMAT — Indicates whether the column storage format is ﬁxed, dynamic, or default. Fixed and dynamic storage formats only apply to columns in NDB tables. The value for all other tables is Default. The SHOW COLUMNS command is the SHOW command that shares the most information with the COLUMNS system view. It accepts the LIKE and WHERE extensions, and will show three extra ﬁelds when the SHOW FULL COLUMNS syntax is used: mysql> SHOW COLUMNS FROM sakila.staff LIKE ’s%’\G *************************** 1. row *************************** Field: staff_id Type: tinyint(3) unsigned Null: NO Key: PRI Default: NULL Extra: auto_increment *************************** 2. row *************************** Field: store_id Type: tinyint(3) unsigned Null: NO Key: MUL Default: NULL Extra: 2 rows in set (0.00 sec) mysql> SHOW FULL COLUMNS FROM sakila.staff LIKE ’s%’\G *************************** 1. row *************************** Field: staff_id Type: tinyint(3) unsigned Collation: NULL Null: NO Key: PRI Default: NULL Extra: auto_increment Privileges: select,insert,update,references Comment: *************************** 2. row *************************** Field: store_id Type: tinyint(3) unsigned Collation: NULL Null: NO Key: MUL Default: NULL Extra: 678
The MySQL Data Dictionary 21 Privileges: select,insert,update,references Comment: 2 rows in set (0.00 sec) The ﬁelds from SHOW COLUMNS and SHOW FULL COLUMNS are: ■ Field — Equivalent to COLUMN_NAME. ■ Type — Equivalent to COLUMN_TYPE. ■ Collation — Equivalent to COLLATION_NAME. SHOW FULL COLUMNS only. ■ Null — Equivalent to IS_NULLABLE. ■ Key — Equivalent to COLUMN_KEY. ■ Default — Equivalent to COLUMN_DEFAULT. ■ Extra — Equivalent to EXTRA. ■ Privileges — Equivalent to PRIVILEGES. SHOW FULL COLUMNS only. ■ Comment — Equivalent to COLUMN_COMMENT. SHOW FULL COLUMNS only. SHOW COLUMNS returns the ﬁelds in the order in which they appear in the table. To guarantee that ordering with the COLUMNS system view, add ORDER BY ORDINAL_POSITION to queries. STATISTICS Information about indexes is stored in the STATISTICS system view, which has one row for each ﬁeld in an index. STATISTICS has the following ﬁelds: ■ TABLE_CATALOG — Provided for standards compliance. However, because MySQL does not have catalogs, this value is always NULL. ■ TABLE_SCHEMA — The name of the database that contains the table that is associated with this index, such as sakila. ■ TABLE_NAME — The name of the table that is associated with this index. ■ NON_UNIQUE — Whether or not the index is unique. The value is 0 for unique indexes and 1 for non-unique indexes. ■ INDEX_SCHEMA — The name of the database that contains this index. This is always the same as TABLE_SCHEMA. ■ INDEX_NAME — The name of the index. ■ SEQ_IN_INDEX — The position of this ﬁeld in the index. The ﬁrst ﬁeld in an index has a value of 1, the second ﬁeld in an index has a value of 2, and so on. For example, the sakila.rental table has an index deﬁned as: UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`, `customer_id`), 679
Part IV Extending Your Skills This index is represented by three rows in the STATISTICS system view: mysql> SELECT SEQ_IN_INDEX, COLUMN_NAME -> FROM STATISTICS -> WHERE TABLE_SCHEMA=’sakila’ -> AND TABLE_NAME=’rental’ -> AND INDEX_NAME=’rental_date’; +--------------+--------------+ | SEQ_IN_INDEX | COLUMN_NAME | +--------------+--------------+ | 1 | rental_date | | 2 | inventory_id | | 3 | customer_id | +--------------+--------------+ 3 rows in set (0.00 sec) ■ COLUMN_NAME — The name of the ﬁeld. ■ COLLATION — The collation for this ﬁeld. Currently all records have NULL values. How- ever, in the future, when mysqld supports ascending and descending collations, the value will be A for ascending collations and D for descending collations. ■ CARDINALITY — The cardinality of this ﬁeld. The cardinality of a ﬁeld is the number of unique values in that ﬁeld. This value may be an estimation, depending on the storage engine of the table. If the cardinality is small compared to the number of rows in the table, it means that there are many repeating values. This means a query will return multiple values for a ﬁeld when searching on a value that is indexed, but repeating. When the ﬁeld cardinality is large, approaching the value of the number of rows, it means that there are very few repeating values. Fields in a unique index have a cardinality equal to the number of rows in the table. ■ SUB_PART — The number of characters in the preﬁx of the index for this ﬁeld. If the index does not contain a preﬁx for this ﬁeld, the value is NULL. For example, the sakila.film table has the following index deﬁned: KEY `idx_title` (`title`(191)), And the value of SUB_PART is 191: mysql> SELECT INDEX_NAME, COLUMN_NAME, SUB_PART -> FROM STATISTICS -> WHERE TABLE_SCHEMA=’sakila’ -> AND TABLE_NAME=’film’ -> AND INDEX_NAME=’idx_title’; +------------+-------------+----------+ | INDEX_NAME | COLUMN_NAME | SUB_PART | +------------+-------------+----------+ | idx_title | title | 191 | +------------+-------------+----------+ 1 row in set (0.00 sec) 680
The MySQL Data Dictionary 21 ■ PACKED — Whether or not the index is packed. This only applies to MyISAM tables. If the index is not packed, the value is NULL. If the index is packed, the value is 0 (nothing packed), 1 (strings and integers are packed), or DEFAULT (only strings are packed). ■ NULLABLE — Whether or not the ﬁeld can contain NULL values. If the ﬁeld can contain NULL values, the value is YES. If the ﬁeld cannot contain NULL values, the value is the empty string (‘’). ■ INDEX_TYPE — The type of index. Example values are BTREE, FULLTEXT, HASH, and RTREE. ■ COMMENT — Always the empty string (‘’). ■ INDEX_COMMENT — The comment deﬁned by the index_comment option when creating or changing an index. The output of SHOW INDEX is very similar to the ﬁelds in the STATISTICS system view: ■ Table — Equivalent to TABLE_NAME. ■ Non_unique — Equivalent to NON_UNIQUE. ■ Key_name — Equivalent to INDEX_NAME. ■ Seq_in_index, Column_name, Collation, Cardinality, Sub_part, Packed, Null, Index_type, Comment, and Index_Comment — Equivalent to SEQ_IN_INDEX, COLUMN_NAME, COLLATION, CARDINALITY, SUB_PART, PACKED, NULLABLE, INDEX_TYPE, COMMENT, and INDEX_COMMENT. TABLE_CONSTRAINTS Unique and primary keys restrict data values in a table, only allowing one set of values for the ﬁelds in those indexes. A foreign key restricts the allowable data for ﬁelds in a table by only allowing values from another set of ﬁelds. The restrictions that unique, primary, and foreign keys place on tables are referred to as table constraints. The TABLE_CONSTRAINTS system view has information about unique keys, primary keys, and foreign keys in the following ﬁelds: ■ CONSTRAINT_CATALOG — Provided for standards compliance. However, because MySQL does not have catalogs, this value is always NULL. ■ CONSTRAINT_SCHEMA — The name of the database that the constraint belongs to, such as sakila. This is the same as the TABLE_SCHEMA ﬁeld. ■ CONSTRAINT_NAME — The name of the index. The value is PRIMARY for a primary key. Unique keys and foreign keys have names that can be set when an index is created or changed. The default name for foreign keys starts with fk_. ■ TABLE_SCHEMA — The name of the database that the constrained table belongs to. ■ TABLE_NAME — The name of the table constrained. ■ CONSTRAINT_TYPE — The type of constraint. Either PRIMARY KEY, UNIQUE, or FOREIGN KEY. 681
Part IV Extending Your Skills There is no equivalent SHOW statement for the data in TABLE_CONSTRAINTS, but the output of SHOW INDEX has some of the same ﬁelds: ■ Table — Equivalent to TABLE_NAME. ■ Key_name — Equivalent to CONSTRAINT_NAME for unique and primary keys. For foreign keys, the Key_name is the name of the index on the ﬁeld, and the CONSTRAINT_NAME is the name of the foreign key constraint. As an example, in the sakila.staff table, the address_id ﬁeld is a foreign key, deﬁned with: KEY idx_fk_address_id (address_id), CONSTRAINT fk_staff_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON UPDATE CASCADE, The Key_name from SHOW INDEX is idx_fk_address_id, and the CONSTRAINT_NAME that appears in the TABLE_CONSTRAINTS system view is fk_staff_address. ■ Column_name — Equivalent to COLUMN_NAME. Note that SHOW INDEX shows all indexes and TABLE_CONSTRAINTS has information only for unique, primary, and foreign keys. KEY_COLUMN_USAGE Like the TABLE_CONSTRAINTS system view, the KEY_COLUMN_USAGE system view only shows information about unique keys, primary keys, and foreign keys. The ﬁelds in KEY_COLUMN_USAGE are: ■ CONSTRAINT_CATALOG — Provided for standards compliance. However, because MySQL does not have catalogs, this value is always NULL. ■ CONSTRAINT_SCHEMA — The name of the database that the constraint belongs to, such as sakila. This is the same as the TABLE_SCHEMA ﬁeld. ■ CONSTRAINT_NAME — The name of the index. The value is PRIMARY for a primary key. Unique keys and foreign keys have names that can be set when an index is created or changed. The default name for foreign keys starts with fk_. ■ TABLE_CATALOG — Provided for standards compliance. However, because MySQL does not have catalogs, this value is always NULL. ■ TABLE_SCHEMA — The name of the database that the constrained table belongs to. ■ TABLE_NAME — The name of the table constrained. ■ COLUMN_NAME — The name of the ﬁeld constrained. ■ ORDINAL_POSITION — The constrained ﬁeld’s position in the index. The ﬁrst ﬁeld in an index has a value of 1, the second ﬁeld has a value of 2, and so on. For example, the sakila.rental table has a composite unique index (a unique index with more than one ﬁeld): UNIQUE KEY rental_date (rental_date, inventory_id, customer_id) 682
The MySQL Data Dictionary 21 The ORDINAL_POSITION of rental_date is 1, of inventory_id is 2, and of customer_id is 3. ■ POSITION_IN_UNIQUE_CONSTRAINT — NULL for keys that are not foreign keys. For foreign keys, the value is the referenced ﬁeld’s position in the foreign key. As an example, the foreign key fk_staff_address on the sakila.staff table is deﬁned as: CONSTRAINT fk_staff_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON UPDATE CASCADE The value of POSITION_IN_UNIQUE_CONSTRAINT is 1, referring to the fact that the address_id ﬁeld is the ﬁrst referenced ﬁeld. Foreign keys are usually deﬁned as: FOREIGN KEY (fld1,fld2) REFERENCES reftbl (reffld1,reffld2) Thus, the POSITION_IN_UNIQUE_CONSTRAINT is the same as ORDINAL_POSITION. ■ REFERENCED_TABLE_SCHEMA — NULL for keys that are not foreign keys. For foreign keys, the database name of the referenced table. As an example, the foreign key fk_staff_address on the sakila.staff table is deﬁned as: CONSTRAINT fk_staff_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON UPDATE CASCADE The REFERENCED_TABLE_SCHEMA is sakila, which is the database containing the address table. ■ REFERENCED_TABLE_NAME — NULL for keys that are not foreign keys. For foreign keys, the name of the referenced table. In the previous example, the value is address. ■ REFERENCED_COLUMN_NAME — NULL for keys that are not foreign keys. For foreign keys, the name of the referenced ﬁeld. In the previous example, the value is address_id. There is no equivalent SHOW statement for the data in KEY_COLUMN_USAGE, but the output of SHOW INDEX has some of the same ﬁelds: ■ Table — Equivalent to TABLE_NAME. ■ Key_name — Equivalent to CONSTRAINT_NAME for unique and primary keys. For foreign keys, the Key_name is the name of the index on the ﬁeld, and the CONSTRAINT_NAME is the name of the foreign key constraint. As an example, in the sakila.staff table, the address_id ﬁeld is a foreign key, deﬁned with: KEY idx_fk_address_id (address_id), CONSTRAINT fk_staff_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON UPDATE CASCADE, The Key_name from SHOW INDEX is idx_fk_address_id, and the CONSTRAINT_NAME that appears in the TABLE_CONSTRAINTS system view is fk_staff_address. ■ Column_name — Equivalent to COLUMN_NAME. 683
Part IV Extending Your Skills Note that SHOW INDEX shows all indexes and KEY_COLUMN_USAGE has information only for unique, primary, and foreign keys. REFERENTIAL_CONSTRAINTS There is more information about foreign keys than the KEY_COLUMN_USAGE and TABLE_ CONSTRAINTS system views show. The REFERENTIAL_CONSTRAINTS system view shows the behavior of a foreign key during updates and deletes. These behaviors are deﬁned in the foreign key constraint with the ON UPDATE and ON DELETE clauses. The REFERENTIAL_CONSTRAINTS system view also repeats the constraint and referenced constraint: ■ CONSTRAINT_CATALOG — Provided for standards compliance. However, because MySQL does not have catalogs, this value is always NULL. ■ CONSTRAINT_SCHEMA — The name of the database that the constraint belongs to, such as sakila. ■ CONSTRAINT_NAME — The name of the index. The value is PRIMARY for a primary key. Unique keys and foreign keys have names that can be set when an index is created or changed. The default name for foreign keys starts with fk_. ■ UNIQUE_CONSTRAINT_CATALOG — Provided for standards compliance. However, because MySQL does not have catalogs, this value is always NULL. ■ UNIQUE_CONSTRAINT_SCHEMA — The database name of the referenced table. This is equivalent to the REFERENCED_TABLE_SCHEMA in the KEY_COLUMN_USAGE system view. ■ UNIQUE_CONSTRAINT_NAME — The name of the referenced constraint. Similar to the CONSTRAINT_NAME ﬁeld of the KEY_COLUMN_USAGE system view, this value is PRIMARY for a primary key or the name of the index for non-primary keys. ■ MATCH_OPTION — This feature has not been implemented yet, so the value is always NONE. ■ UPDATE_RULE — The update behavior of the foreign key, as set by the foreign key deﬁni- tion. Possible values are CASCADE, NO ACTION, RESTRICT, SET NULL, SET DEFAULT. See Chapter 6 for more information on deﬁning foreign keys. ■ DELETE_RULE — The delete behavior of the foreign key, as set by the foreign key deﬁni- tion. Possible values are CASCADE, NO ACTION, RESTRICT, SET NULL, SET DEFAULT. See Chapter 6 for more information on deﬁning foreign keys. ■ TABLE_NAME — The table name that the foreign key constraint belongs to. ■ REFERENCED_TABLE_NAME — The table name of the referenced table. There is no complete SHOW statement for the ﬁelds in REFERENTIAL_CONSTRAINTS, but the Key_name ﬁeld from the output of SHOW INDEX is equivalent to CONSTRAINT_NAME. 684
The MySQL Data Dictionary 21 TRIGGERS Triggers are user-deﬁned stored SQL that get run when data changes. Triggers are deﬁned on a per-table basis, and can occur before and after inserts, updates, and deletes. The ﬁelds in the TRIGGERS system view are: ■ TRIGGER_CATALOG — Provided for standards compliance. However, because MySQL does not have catalogs, this value is always NULL. ■ TRIGGER_SCHEMA — The name of the database that the trigger belongs to, such as sakila. ■ TRIGGER_NAME — The name of the trigger. ■ EVENT_MANIPULATION — The name of the action that calls this trigger. Possible values are INSERT, DELETE, or UPDATE. ■ EVENT_OBJECT_CATALOG — Provided for standards compliance. However, because MySQL does not have catalogs, this value is always NULL. ■ EVENT_OBJECT_SCHEMA — The name of the database that the table associated with the trigger belongs to, such as sakila. ■ EVENT_OBJECT_TABLE — The name of the table associated with the trigger. ■ ACTION_ORDER — The order the trigger is run in. This value is always 0 because MySQL only supports one trigger per set of ACTION_TIMING and EVENT_MANIPULATION. ■ ACTION_CONDITION — Always NULL. ■ ACTION_STATEMENT — The SQL that is run by the trigger. ■ ACTION_ORIENTATION — Always ROW. ■ ACTION_TIMING — When the trigger occurs. Possible values are BEFORE or AFTER. ■ ACTION_REFERENCE_OLD_TABLE — Always NULL. ■ ACTION_REFERENCE_NEW_TABLE — Always NULL. ■ ACTION_REFERENCE_OLD_ROW — How the trigger references the row prior to the data change. This is not settable by the user, and is always OLD. ■ ACTION_REFERENCE_NEW_ROW — How the trigger references the row after the data is changed. This is not settable by the user, and is always NEW. ■ CREATED — Always NULL. ■ SQL_MODE — Stores the environmental sql_mode as it was when the trigger was created. This is also the sql_mode that is used when the trigger is invoked. ■ DEFINER — The trigger deﬁner, in the MySQL user@host format. 685
Part IV Extending Your Skills ■ CHARACTER_SET_CLIENT — Stores the environmental character set as it was when the trigger was created. This is also the character_set_client that is used when the trig- ger is invoked. ■ COLLATION_CONNECTION — Stores the environmental collation as it was when the trig- ger was created. This is also the collation_connection that is used when the trigger is invoked. ■ DATABASE_COLLATION — The default collation of the database associated with the trigger. For more information on triggers, see Chapter 7. The SHOW TRIGGERS statement outputs some of the same information in the TRIGGERS system view. The equivalent ﬁelds are: ■ Trigger — Equivalent to TRIGGER_NAME. ■ Event — Equivalent to EVENT_MANIPULATION. ■ Table — Equivalent to EVENT_OBJECT_TABLE. ■ Statement — Equivalent to ACTION_STATEMENT. ■ Timing — Equivalent to ACTION_TIMING. ■ Created, sql_mode, Definer, character_set_client, collation connection — Equivalent to CREATED, SQL_MODE, DEFINER, CHARACTER_SET_CLIENT, COLLATION_CONNECTION. The SHOW CREATE TRIGGER statement outputs the following ﬁelds, which are similar to ﬁelds in the TRIGGERS system view: ■ Trigger — Equivalent to TRIGGER_NAME. ■ sql_mode — Equivalent to SQL_MODE. ■ SQL Original Statement — This is the entire CREATE TRIGGER statement, which includes the information shown in the EVENT_MANIPULATION, ACTION_ORDER, ACTION_STATEMENT, ACTION_TIMING, and DEFINER ﬁelds. ■ character_set_client, collation connection, Database Collation — Equivalent to CHARACTER_SET_CLIENT, COLLATION_CONNECTION, and DATABASE_ COLLATION. ROUTINES The ROUTINES system view stores metadata about stored procedures and stored routines. For more information on stored routines, see Chapter 7. The ﬁelds in the ROUTINES system view are: ■ SPECIFIC_NAME — The name of the stored routine. ■ ROUTINE_CATALOG — Provided for standards compliance. However, because MySQL does not have catalogs, this value is always NULL. 686