MySQL Tutorial/Data Dictionary/INFORMATION SCHEMA — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:44, 26 мая 2010
Содержание
- 1 The INFORMATION_SCHEMA.CHARACTER_SETS Table
- 2 The INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY Table
- 3 The INFORMATION_SCHEMA.COLLATIONS Table
- 4 The INFORMATION_SCHEMA.COLUMN_PRIVILEGES Table
- 5 The INFORMATION_SCHEMA.COLUMNS Table
- 6 The INFORMATION_SCHEMA Database
- 7 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table
- 8 The INFORMATION_SCHEMA.PROFILING Table
- 9 The INFORMATION_SCHEMA ROUTINES Table
- 10 The INFORMATION_SCHEMA.SCHEMA_PRIVILEGES Table
- 11 The INFORMATION_SCHEMA.SCHEMATA Table
- 12 The INFORMATION_SCHEMA.STATISTICS Table
- 13 The INFORMATION_SCHEMA.TABLE_CONSTRAINTS Table
- 14 The INFORMATION_SCHEMA.TABLE_PRIVILEGES Table
- 15 The INFORMATION_SCHEMA.TABLES Table
- 16 The INFORMATION_SCHEMA.TRIGGERS Table
- 17 The INFORMATION_SCHEMA.USER_PRIVILEGES Table
- 18 The INFORMATION_SCHEMA.VIEWS Table
The INFORMATION_SCHEMA.CHARACTER_SETS Table
The CHARACTER_SETS table provides information about available character sets.
The following statements are equivalent:
mysql> desc INFORMATION_SCHEMA.CHARACTER_SETS;
+----------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+-------------+------+-----+---------+-------+
| CHARACTER_SET_NAME | varchar(64) | NO | | | |
| DEFAULT_COLLATE_NAME | varchar(64) | NO | | | |
| DESCRIPTION | varchar(60) | NO | | | |
| MAXLEN | bigint(3) | NO | | 0 | |
+----------------------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)
mysql>
mysql> SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS;
+--------------------+----------------------+-----------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+-----------------------------+--------+
| big5 | big5_chinese_ci | Big5 Traditional Chinese | 2 |
| dec8 | dec8_swedish_ci | DEC West European | 1 |
| cp850 | cp850_general_ci | DOS West European | 1 |
| hp8 | hp8_english_ci | HP West European | 1 |
| koi8r | koi8r_general_ci | KOI8-R Relcom Russian | 1 |
| latin1 | latin1_swedish_ci | cp1252 West European | 1 |
| latin2 | latin2_general_ci | ISO 8859-2 Central European | 1 |
| swe7 | swe7_swedish_ci | 7bit Swedish | 1 |
| ascii | ascii_general_ci | US ASCII | 1 |
| ujis | ujis_japanese_ci | EUC-JP Japanese | 3 |
| sjis | sjis_japanese_ci | Shift-JIS Japanese | 2 |
| hebrew | hebrew_general_ci | ISO 8859-8 Hebrew | 1 |
| tis620 | tis620_thai_ci | TIS620 Thai | 1 |
| euckr | euckr_korean_ci | EUC-KR Korean | 2 |
| koi8u | koi8u_general_ci | KOI8-U Ukrainian | 1 |
| gb2312 | gb2312_chinese_ci | GB2312 Simplified Chinese | 2 |
| greek | greek_general_ci | ISO 8859-7 Greek | 1 |
| cp1250 | cp1250_general_ci | Windows Central European | 1 |
| gbk | gbk_chinese_ci | GBK Simplified Chinese | 2 |
| latin5 | latin5_turkish_ci | ISO 8859-9 Turkish | 1 |
| armscii8 | armscii8_general_ci | ARMSCII-8 Armenian | 1 |
| utf8 | utf8_general_ci | UTF-8 Unicode | 3 |
| ucs2 | ucs2_general_ci | UCS-2 Unicode | 2 |
| cp866 | cp866_general_ci | DOS Russian | 1 |
| keybcs2 | keybcs2_general_ci | DOS Kamenicky Czech-Slovak | 1 |
| macce | macce_general_ci | Mac Central European | 1 |
| macroman | macroman_general_ci | Mac West European | 1 |
| cp852 | cp852_general_ci | DOS Central European | 1 |
| latin7 | latin7_general_ci | ISO 8859-13 Baltic | 1 |
| cp1251 | cp1251_general_ci | Windows Cyrillic | 1 |
| cp1256 | cp1256_general_ci | Windows Arabic | 1 |
| cp1257 | cp1257_general_ci | Windows Baltic | 1 |
| binary | binary | Binary pseudo charset | 1 |
| geostd8 | geostd8_general_ci | GEOSTD8 Georgian | 1 |
| cp932 | cp932_japanese_ci | SJIS for Windows Japanese | 2 |
| eucjpms | eucjpms_japanese_ci | UJIS for Windows Japanese | 3 |
+--------------------+----------------------+-----------------------------+--------+
36 rows in set (0.02 sec)
mysql>
mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+----------+-----------------------------+---------------------+--------+
36 rows in set (0.00 sec)
The INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY Table
The COLLATION_CHARACTER_SET_APPLICABILITY table indicates what character set is applicable for what collation.
The columns are equivalent to the first two display fields that we get from SHOW COLLATION.
mysql> desc INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY;
+--------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| COLLATION_NAME | varchar(64) | NO | | | |
| CHARACTER_SET_NAME | varchar(64) | NO | | | |
+--------------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
The INFORMATION_SCHEMA.COLLATIONS Table
The COLLATIONS table provides information about collations for each character set.
mysql>
mysql> desc INFORMATION_SCHEMA.COLLATIONS;
+--------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| COLLATION_NAME | varchar(64) | NO | | | |
| CHARACTER_SET_NAME | varchar(64) | NO | | | |
| ID | bigint(11) | NO | | 0 | |
| IS_DEFAULT | varchar(3) | NO | | | |
| IS_COMPILED | varchar(3) | NO | | | |
| SORTLEN | bigint(3) | NO | | 0 | |
+--------------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
The INFORMATION_SCHEMA.COLUMN_PRIVILEGES Table
The COLUMN_PRIVILEGES table provides information about column privileges.
PRIVILEGE_TYPE can contain one (and only one) of these values: SELECT, INSERT, UPDATE, REFERENCES.
This information comes from the mysql.columns_priv grant table.
25. 8. INFORMATION_SCHEMA 25. 8. 1. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMADatabase.htm">The INFORMATION_SCHEMA Database</a> 25. 8. 2. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMASCHEMATATable.htm">The INFORMATION_SCHEMA.SCHEMATA Table</a> 25. 8. 3. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMATABLESTable.htm">The INFORMATION_SCHEMA.TABLES Table</a> 25. 8. 4. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMACOLUMNSTable.htm">The INFORMATION_SCHEMA.COLUMNS Table</a> 25. 8. 5. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMASTATISTICSTable.htm">The INFORMATION_SCHEMA.STATISTICS Table</a> 25. 8. 6. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMAUSERPRIVILEGESTable.htm">The INFORMATION_SCHEMA.USER_PRIVILEGES Table</a> 25. 8. 7. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMASCHEMAPRIVILEGESTable.htm">The INFORMATION_SCHEMA.SCHEMA_PRIVILEGES Table</a> 25. 8. 8. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMATABLEPRIVILEGESTable.htm">The INFORMATION_SCHEMA.TABLE_PRIVILEGES Table</a> 25. 8. 9. The INFORMATION_SCHEMA.COLUMN_PRIVILEGES Table 25. 8. 10. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMACHARACTERSETSTable.htm">The INFORMATION_SCHEMA.CHARACTER_SETS Table</a> 25. 8. 11. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMACOLLATIONSTable.htm">The INFORMATION_SCHEMA.COLLATIONS Table</a> 25. 8. 12. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMACOLLATIONCHARACTERSETAPPLICABILITYTable.htm">The INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY Table</a> 25. 8. 13. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMATABLECONSTRAINTSTable.htm">The INFORMATION_SCHEMA.TABLE_CONSTRAINTS Table</a> 25. 8. 14. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMAKEYCOLUMNUSAGETable.htm">The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table</a> 25. 8. 15. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMAROUTINESTable.htm">The INFORMATION_SCHEMA ROUTINES Table</a> 25. 8. 16. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMAVIEWSTable.htm">The INFORMATION_SCHEMA.VIEWS Table</a> 25. 8. 17. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMATRIGGERSTable.htm">The INFORMATION_SCHEMA.TRIGGERS Table</a> 25. 8. 18. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMAPROFILINGTable.htm">The INFORMATION_SCHEMA.PROFILING Table</a>
The INFORMATION_SCHEMA.COLUMNS Table
The COLUMNS table provides information about columns in tables.
mysql> desc INFORMATION_SCHEMA.COLUMNS;
+--------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+--------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | YES | | NULL | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| COLUMN_NAME | varchar(64) | NO | | | |
| ORDINAL_POSITION | bigint(21) | NO | | 0 | |
| COLUMN_DEFAULT | longtext | YES | | NULL | |
| IS_NULLABLE | varchar(3) | NO | | | |
| DATA_TYPE | varchar(64) | NO | | | |
| CHARACTER_MAXIMUM_LENGTH | bigint(21) | YES | | NULL | |
| CHARACTER_OCTET_LENGTH | bigint(21) | YES | | NULL | |
| NUMERIC_PRECISION | bigint(21) | YES | | NULL | |
| NUMERIC_SCALE | bigint(21) | YES | | NULL | |
| CHARACTER_SET_NAME | varchar(64) | YES | | NULL | |
| COLLATION_NAME | varchar(64) | YES | | NULL | |
| COLUMN_TYPE | longtext | NO | | | |
| COLUMN_KEY | varchar(3) | NO | | | |
| EXTRA | varchar(20) | NO | | | |
| PRIVILEGES | varchar(80) | NO | | | |
| COLUMN_COMMENT | varchar(255) | NO | | | |
+--------------------------+--------------+------+-----+---------+-------+
19 rows in set (0.02 sec)
The INFORMATION_SCHEMA Database
INFORMATION_SCHEMA provides access to database metadata.
Metadata is data about the data.
For example, the name of a database or table, the data type of a column, or access privileges.
INFORMATION_SCHEMA is the information database.
Inside INFORMATION_SCHEMA there are several read-only tables.
They are actually views.
It is possible to select INFORMATION_SCHEMA as the default database with a USE statement.
SELECT ... FROM INFORMATION_SCHEMA statement is a more consistent way to provide access to the information provided by the various SHOW statements that.
mysql> CREATE TABLE Employee(
-> id int,
-> first_name VARCHAR(15),
-> last_name VARCHAR(15),
-> start_date DATE,
-> end_date DATE,
-> salary FLOAT(8,2),
-> city VARCHAR(10),
-> description VARCHAR(15)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> SELECT table_name, table_type, engine
-> FROM information_schema.tables;
+---------------------------------------+-------------+--------+
| table_name | table_type | engine |
+---------------------------------------+-------------+--------+
| CHARACTER_SETS | SYSTEM VIEW | MEMORY |
| COLLATIONS | SYSTEM VIEW | MEMORY |
| COLLATION_CHARACTER_SET_APPLICABILITY | SYSTEM VIEW | MEMORY |
| COLUMNS | SYSTEM VIEW | MyISAM |
| COLUMN_PRIVILEGES | SYSTEM VIEW | MEMORY |
| KEY_COLUMN_USAGE | SYSTEM VIEW | MEMORY |
| PROFILING | SYSTEM VIEW | MEMORY |
| ROUTINES | SYSTEM VIEW | MyISAM |
| SCHEMATA | SYSTEM VIEW | MEMORY |
| SCHEMA_PRIVILEGES | SYSTEM VIEW | MEMORY |
| STATISTICS | SYSTEM VIEW | MEMORY |
| TABLES | SYSTEM VIEW | MEMORY |
| TABLE_CONSTRAINTS | SYSTEM VIEW | MEMORY |
| TABLE_PRIVILEGES | SYSTEM VIEW | MEMORY |
| TRIGGERS | SYSTEM VIEW | MyISAM |
| USER_PRIVILEGES | SYSTEM VIEW | MEMORY |
| VIEWS | SYSTEM VIEW | MyISAM |
| columns_priv | BASE TABLE | MyISAM |
| db | BASE TABLE | MyISAM |
| func | BASE TABLE | MyISAM |
| help_category | BASE TABLE | MyISAM |
| help_keyword | BASE TABLE | MyISAM |
| help_relation | BASE TABLE | MyISAM |
| help_topic | BASE TABLE | MyISAM |
| host | BASE TABLE | MyISAM |
| proc | BASE TABLE | MyISAM |
| procs_priv | BASE TABLE | MyISAM |
| tables_priv | BASE TABLE | MyISAM |
| time_zone | BASE TABLE | MyISAM |
| time_zone_leap_second | BASE TABLE | MyISAM |
| time_zone_name | BASE TABLE | MyISAM |
| time_zone_transition | BASE TABLE | MyISAM |
| time_zone_transition_type | BASE TABLE | MyISAM |
| user | BASE TABLE | MyISAM |
| employee | BASE TABLE | MyISAM |
| v | VIEW | NULL |
+---------------------------------------+-------------+--------+
36 rows in set (0.01 sec)
mysql>
mysql>
mysql>
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table
The KEY_COLUMN_USAGE table describes which key columns have constraints.
mysql>
mysql>
mysql> desc INFORMATION_SCHEMA.KEY_COLUMN_USAGE;
+-------------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG | varchar(512) | YES | | NULL | |
| CONSTRAINT_SCHEMA | varchar(64) | NO | | | |
| CONSTRAINT_NAME | varchar(64) | NO | | | |
| TABLE_CATALOG | varchar(512) | YES | | NULL | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| COLUMN_NAME | varchar(64) | NO | | | |
| ORDINAL_POSITION | bigint(10) | NO | | 0 | |
| POSITION_IN_UNIQUE_CONSTRAINT | bigint(10) | YES | | NULL | |
| REFERENCED_TABLE_SCHEMA | varchar(64) | YES | | NULL | |
| REFERENCED_TABLE_NAME | varchar(64) | YES | | NULL | |
| REFERENCED_COLUMN_NAME | varchar(64) | YES | | NULL | |
+-------------------------------+--------------+------+-----+---------+-------+
12 rows in set (0.00 sec)
The INFORMATION_SCHEMA.PROFILING Table
mysql> desc INFORMATION_SCHEMA.PROFILING;
+---------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-------------+------+-----+---------+-------+
| QUERY_ID | bigint(20) | NO | | 0 | |
| SEQ | bigint(20) | NO | | 0 | |
| STATE | varchar(30) | NO | | | |
| DURATION | double | NO | | 0 | |
| CPU_USER | double | YES | | NULL | |
| CPU_SYSTEM | double | YES | | NULL | |
| CONTEXT_VOLUNTARY | bigint(20) | YES | | NULL | |
| CONTEXT_INVOLUNTARY | bigint(20) | YES | | NULL | |
| BLOCK_OPS_IN | bigint(20) | YES | | NULL | |
| BLOCK_OPS_OUT | bigint(20) | YES | | NULL | |
| MESSAGES_SENT | bigint(20) | YES | | NULL | |
| MESSAGES_RECEIVED | bigint(20) | YES | | NULL | |
| PAGE_FAULTS_MAJOR | bigint(20) | YES | | NULL | |
| PAGE_FAULTS_MINOR | bigint(20) | YES | | NULL | |
| SWAPS | bigint(20) | YES | | NULL | |
| SOURCE_FUNCTION | varchar(30) | YES | | NULL | |
| SOURCE_FILE | varchar(20) | YES | | NULL | |
| SOURCE_LINE | bigint(20) | YES | | NULL | |
+---------------------+-------------+------+-----+---------+-------+
18 rows in set (0.03 sec)
The INFORMATION_SCHEMA ROUTINES Table
The ROUTINES table provides information about procedures and functions.
The column named "mysql.proc name" indicates the mysql.proc table column that corresponds to the INFORMATION_SCHEMA.ROUTINES table column, if any.
mysql> desc INFORMATION_SCHEMA.ROUTINES;
+--------------------+--------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------------------+-------+
| SPECIFIC_NAME | varchar(64) | NO | | | |
| ROUTINE_CATALOG | varchar(512) | YES | | NULL | |
| ROUTINE_SCHEMA | varchar(64) | NO | | | |
| ROUTINE_NAME | varchar(64) | NO | | | |
| ROUTINE_TYPE | varchar(9) | NO | | | |
| DTD_IDENTIFIER | varchar(64) | YES | | NULL | |
| ROUTINE_BODY | varchar(8) | NO | | | |
| ROUTINE_DEFINITION | longtext | YES | | NULL | |
| EXTERNAL_NAME | varchar(64) | YES | | NULL | |
| EXTERNAL_LANGUAGE | varchar(64) | YES | | NULL | |
| PARAMETER_STYLE | varchar(8) | NO | | | |
| IS_DETERMINISTIC | varchar(3) | NO | | | |
| SQL_DATA_ACCESS | varchar(64) | NO | | | |
| SQL_PATH | varchar(64) | YES | | NULL | |
| SECURITY_TYPE | varchar(7) | NO | | | |
| CREATED | datetime | NO | | 0000-00-00 00:00:00 | |
| LAST_ALTERED | datetime | NO | | 0000-00-00 00:00:00 | |
| SQL_MODE | longtext | NO | | | |
| ROUTINE_COMMENT | varchar(64) | NO | | | |
| DEFINER | varchar(77) | NO | | | |
+--------------------+--------------+------+-----+---------------------+-------+
20 rows in set (0.02 sec)
The INFORMATION_SCHEMA.SCHEMA_PRIVILEGES Table
The SCHEMA_PRIVILEGES table provides information about schema (database) privileges.
This information comes from the mysql.db grant table.
mysql>
mysql> desc INFORMATION_SCHEMA.SCHEMA_PRIVILEGES;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| GRANTEE | varchar(81) | NO | | | |
| TABLE_CATALOG | varchar(512) | YES | | NULL | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| PRIVILEGE_TYPE | varchar(64) | NO | | | |
| IS_GRANTABLE | varchar(3) | NO | | | |
+----------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
The INFORMATION_SCHEMA.SCHEMATA Table
A schema is a database, so the SCHEMATA table provides information about databases.
mysql> desc INFORMATION_SCHEMA.SCHEMATA;
+----------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+--------------+------+-----+---------+-------+
| CATALOG_NAME | varchar(512) | YES | | NULL | |
| SCHEMA_NAME | varchar(64) | NO | | | |
| DEFAULT_CHARACTER_SET_NAME | varchar(64) | NO | | | |
| DEFAULT_COLLATION_NAME | varchar(64) | NO | | | |
| SQL_PATH | varchar(512) | YES | | NULL | |
+----------------------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
The INFORMATION_SCHEMA.STATISTICS Table
The STATISTICS table provides information about table indexes.
mysql> desc INFORMATION_SCHEMA.STATISTICS;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | YES | | NULL | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| NON_UNIQUE | bigint(1) | NO | | 0 | |
| INDEX_SCHEMA | varchar(64) | NO | | | |
| INDEX_NAME | varchar(64) | NO | | | |
| SEQ_IN_INDEX | bigint(2) | NO | | 0 | |
| COLUMN_NAME | varchar(64) | NO | | | |
| COLLATION | varchar(1) | YES | | NULL | |
| CARDINALITY | bigint(21) | YES | | NULL | |
| SUB_PART | bigint(3) | YES | | NULL | |
| PACKED | varchar(10) | YES | | NULL | |
| NULLABLE | varchar(3) | NO | | | |
| INDEX_TYPE | varchar(16) | NO | | | |
| COMMENT | varchar(16) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
15 rows in set (0.02 sec)
The INFORMATION_SCHEMA.TABLE_CONSTRAINTS Table
The TABLE_CONSTRAINTS table describes which tables have constraints.
The CONSTRAINT_TYPE value can be UNIQUE, PRIMARY KEY, or FOREIGN KEY.
The CONSTRAINT_TYPE column can contain one of these values: UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK.
25. 8. INFORMATION_SCHEMA 25. 8. 1. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMADatabase.htm">The INFORMATION_SCHEMA Database</a> 25. 8. 2. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMASCHEMATATable.htm">The INFORMATION_SCHEMA.SCHEMATA Table</a> 25. 8. 3. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMATABLESTable.htm">The INFORMATION_SCHEMA.TABLES Table</a> 25. 8. 4. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMACOLUMNSTable.htm">The INFORMATION_SCHEMA.COLUMNS Table</a> 25. 8. 5. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMASTATISTICSTable.htm">The INFORMATION_SCHEMA.STATISTICS Table</a> 25. 8. 6. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMAUSERPRIVILEGESTable.htm">The INFORMATION_SCHEMA.USER_PRIVILEGES Table</a> 25. 8. 7. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMASCHEMAPRIVILEGESTable.htm">The INFORMATION_SCHEMA.SCHEMA_PRIVILEGES Table</a> 25. 8. 8. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMATABLEPRIVILEGESTable.htm">The INFORMATION_SCHEMA.TABLE_PRIVILEGES Table</a> 25. 8. 9. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMACOLUMNPRIVILEGESTable.htm">The INFORMATION_SCHEMA.COLUMN_PRIVILEGES Table</a> 25. 8. 10. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMACHARACTERSETSTable.htm">The INFORMATION_SCHEMA.CHARACTER_SETS Table</a> 25. 8. 11. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMACOLLATIONSTable.htm">The INFORMATION_SCHEMA.COLLATIONS Table</a> 25. 8. 12. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMACOLLATIONCHARACTERSETAPPLICABILITYTable.htm">The INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY Table</a> 25. 8. 13. The INFORMATION_SCHEMA.TABLE_CONSTRAINTS Table 25. 8. 14. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMAKEYCOLUMNUSAGETable.htm">The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table</a> 25. 8. 15. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMAROUTINESTable.htm">The INFORMATION_SCHEMA ROUTINES Table</a> 25. 8. 16. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMAVIEWSTable.htm">The INFORMATION_SCHEMA.VIEWS Table</a> 25. 8. 17. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMATRIGGERSTable.htm">The INFORMATION_SCHEMA.TRIGGERS Table</a> 25. 8. 18. <A href="/Tutorial/MySQL/0500__Data-Dictionary/TheINFORMATIONSCHEMAPROFILINGTable.htm">The INFORMATION_SCHEMA.PROFILING Table</a>
The INFORMATION_SCHEMA.TABLE_PRIVILEGES Table
The TABLE_PRIVILEGES table provides information about table privileges.
PRIVILEGE_TYPE can contain one (and only one) of these values: SELECT, INSERT, UPDATE, REFERENCES, ALTER, INDEX, DROP, CREATE VIEW.
This information comes from the mysql.tables_priv grant table.
mysql>
mysql> desc INFORMATION_SCHEMA.TABLE_PRIVILEGES;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| GRANTEE | varchar(81) | NO | | | |
| TABLE_CATALOG | varchar(512) | YES | | NULL | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| PRIVILEGE_TYPE | varchar(64) | NO | | | |
| IS_GRANTABLE | varchar(3) | NO | | | |
+----------------+--------------+------+-----+---------+-------+
6 rows in set (0.02 sec)
The INFORMATION_SCHEMA.TABLES Table
The TABLES table provides information about tables in databases.
mysql> desc INFORMATION_SCHEMA.TABLES;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | YES | | NULL | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| TABLE_TYPE | varchar(64) | NO | | | |
| ENGINE | varchar(64) | YES | | NULL | |
| VERSION | bigint(21) | YES | | NULL | |
| ROW_FORMAT | varchar(10) | YES | | NULL | |
| TABLE_ROWS | bigint(21) | YES | | NULL | |
| AVG_ROW_LENGTH | bigint(21) | YES | | NULL | |
| DATA_LENGTH | bigint(21) | YES | | NULL | |
| MAX_DATA_LENGTH | bigint(21) | YES | | NULL | |
| INDEX_LENGTH | bigint(21) | YES | | NULL | |
| DATA_FREE | bigint(21) | YES | | NULL | |
| AUTO_INCREMENT | bigint(21) | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| CHECK_TIME | datetime | YES | | NULL | |
| TABLE_COLLATION | varchar(64) | YES | | NULL | |
| CHECKSUM | bigint(21) | YES | | NULL | |
| CREATE_OPTIONS | varchar(255) | YES | | NULL | |
| TABLE_COMMENT | varchar(80) | NO | | | |
+-----------------+--------------+------+-----+---------+-------+
21 rows in set (0.01 sec)
The INFORMATION_SCHEMA.TRIGGERS Table
The TRIGGERS table provides information about triggers.
mysql> desc INFORMATION_SCHEMA.TRIGGERS;
+----------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+--------------+------+-----+---------+-------+
| TRIGGER_CATALOG | varchar(512) | YES | | NULL | |
| TRIGGER_SCHEMA | varchar(64) | NO | | | |
| TRIGGER_NAME | varchar(64) | NO | | | |
| EVENT_MANIPULATION | varchar(6) | NO | | | |
| EVENT_OBJECT_CATALOG | varchar(512) | YES | | NULL | |
| EVENT_OBJECT_SCHEMA | varchar(64) | NO | | | |
| EVENT_OBJECT_TABLE | varchar(64) | NO | | | |
| ACTION_ORDER | bigint(4) | NO | | 0 | |
| ACTION_CONDITION | longtext | YES | | NULL | |
| ACTION_STATEMENT | longtext | NO | | | |
| ACTION_ORIENTATION | varchar(9) | NO | | | |
| ACTION_TIMING | varchar(6) | NO | | | |
| ACTION_REFERENCE_OLD_TABLE | varchar(64) | YES | | NULL | |
| ACTION_REFERENCE_NEW_TABLE | varchar(64) | YES | | NULL | |
| ACTION_REFERENCE_OLD_ROW | varchar(3) | NO | | | |
| ACTION_REFERENCE_NEW_ROW | varchar(3) | NO | | | |
| CREATED | datetime | YES | | NULL | |
| SQL_MODE | longtext | NO | | | |
| DEFINER | longtext | NO | | | |
+----------------------------+--------------+------+-----+---------+-------+
19 rows in set (0.02 sec)
The INFORMATION_SCHEMA.USER_PRIVILEGES Table
mysql> desc INFORMATION_SCHEMA.USER_PRIVILEGES;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| GRANTEE | varchar(81) | NO | | | |
| TABLE_CATALOG | varchar(512) | YES | | NULL | |
| PRIVILEGE_TYPE | varchar(64) | NO | | | |
| IS_GRANTABLE | varchar(3) | NO | | | |
+----------------+--------------+------+-----+---------+-------+
4 rows in set (0.02 sec)
The INFORMATION_SCHEMA.VIEWS Table
The VIEWS table provides information about views in databases. You must have the SHOW VIEW privilege to access this table.
mysql> desc INFORMATION_SCHEMA.VIEWS;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | YES | | NULL | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| VIEW_DEFINITION | longtext | NO | | | |
| CHECK_OPTION | varchar(8) | NO | | | |
| IS_UPDATABLE | varchar(3) | NO | | | |
| DEFINER | varchar(77) | NO | | | |
| SECURITY_TYPE | varchar(7) | NO | | | |
+-----------------+--------------+------+-----+---------+-------+
8 rows in set (0.02 sec)