MySQL

MySQL is a relatively lightweight DBMS whose features map well to the minimalist approach taken by the Rails designers to database usage. So, it became the "default" database for Rails 1.x. Although this role has passed to SQLite in Rails 2.x, many (probably, most) Rails applications continue to use MySQL.

Databases

Each MySQL installation can manage a number of "databases". These are stored as directories in the OS file system and are accessed (in general) independently. Three databases (information_schema, mysql, test) are present by default; other databases are created to serve the needs of applications. By convention, each Rails app uses one or more sets of databases. In the following examples, only one set (eg, "abc_*") is present:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| abc_development    | 
| abc_production     | 
| abc_test           | 
| information_schema | 
| mysql              | 
| test               | 
+--------------------+

abc_*

In any set of Rails databases, the development database is the most likely to contain tables. After all, it is the database that the developer uses constantly. The production database will only be populated if and when the developer has decided to deploy the app in production mode. Finally, the test database will only be populated if and when the developer runs database-backed tests.

The only table present by default in these databases is schema_info. This is an extremely simple table, containing only the version number for the database.

Tables

mysql> describe schema_info; 
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| version | int(11) | YES  |     | NULL    |       | 
+---------+---------+------+-----+---------+-------+

information_schema

The information_schema database contains general settings about the way that MySQL handles databases, tables, users, etc:

Tables

mysql> describe 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       |       |
+----------------------+-------------+------+-----+---------+-------+

mysql> describe 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       |       | 
+--------------------+-------------+------+-----+---------+-------+

mysql> describe COLLATION_CHARACTER_SET_APPLICABILITY; 
+--------------------+-------------+------+-----+---------+-------+
| Field              | Type        | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| COLLATION_NAME     | varchar(64) | NO   |     |         |       | 
| CHARACTER_SET_NAME | varchar(64) | NO   |     |         |       | 
+--------------------+-------------+------+-----+---------+-------+

mysql> describe 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           | varchar(64)  | 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   |     |         |       | 
+--------------------------+--------------+------+-----+---------+-------+

mysql> describe COLUMN_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   |     |         |       | 
| COLUMN_NAME    | varchar(64)  | NO   |     |         |       | 
| PRIVILEGE_TYPE | varchar(64)  | NO   |     |         |       | 
| IS_GRANTABLE   | varchar(3)   | NO   |     |         |       | 
+----------------+--------------+------+-----+---------+-------+

mysql> describe 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    |       | 
+-------------------------------+--------------+------+-----+---------+-------+

mysql> describe 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   |     |                     |       | 
+--------------------+--------------+------+-----+---------------------+-------+

mysql> describe 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    |       | 
+----------------------------+--------------+------+-----+---------+-------+

mysql> describe 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   |     |         |       | 
+----------------+--------------+------+-----+---------+-------+

mysql> describe 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    |       | 
+---------------+--------------+------+-----+---------+-------+

mysql> describe 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   |     |         |       | 
+-----------------+--------------+------+-----+---------+-------+

mysql> describe TABLE_CONSTRAINTS; 
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG | varchar(512) | YES  |     | NULL    |       | 
| CONSTRAINT_SCHEMA  | varchar(64)  | NO   |     |         |       | 
| CONSTRAINT_NAME    | varchar(64)  | NO   |     |         |       | 
| TABLE_SCHEMA       | varchar(64)  | NO   |     |         |       | 
| TABLE_NAME         | varchar(64)  | NO   |     |         |       | 
| CONSTRAINT_TYPE    | varchar(64)  | NO   |     |         |       | 
+--------------------+--------------+------+-----+---------+-------+

mysql> describe 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   |     |         |       | 
+----------------+--------------+------+-----+---------+-------+

mysql> describe 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   |     |         |       | 
+----------------------------+--------------+------+-----+---------+-------+

mysql> describe 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   |     |         |       | 
+----------------+--------------+------+-----+---------+-------+

mysql> describe 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   |     |         |       | 
+-----------------+--------------+------+-----+---------+-------+

mysql

The mysql database contains miscellaneous types of information: help topics, privileges, time zone information, etc.

Tables

mysql> describe columns_priv; 
+-------------+----------------------------+------+-----+-------------------+-------+
| Field       | Type                       | Null | Key | Default           | Extra |
+-------------+----------------------------+------+-----+-------------------+-------+
| Host        | char(60)                   | NO   | PRI |                   |       | 
| Db          | char(64)                   | NO   | PRI |                   |       | 
| User        | char(16)                   | NO   | PRI |                   |       | 
| Table_name  | char(64)                   | NO   | PRI |                   |       | 
| Column_name | char(64)                   | NO   | PRI |                   |       | 
| Timestamp   | timestamp                  | NO   |     | CURRENT_TIMESTAMP |       | 
| Column_priv | set('Select','Insert',...) | NO   |     |                   |       | 
+-------------+----------------------------+------+-----+-------------------+-------+

mysql> describe db; 
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       | 
| Db                    | char(64)      | NO   | PRI |         |       | 
| User                  | char(16)      | NO   | PRI |         |       | 
| Select_priv           | enum('N','Y') | NO   |     | N       |       | 
| Insert_priv           | enum('N','Y') | NO   |     | N       |       | 
| Update_priv           | enum('N','Y') | NO   |     | N       |       | 
| Delete_priv           | enum('N','Y') | NO   |     | N       |       | 
| Create_priv           | enum('N','Y') | NO   |     | N       |       | 
| Drop_priv             | enum('N','Y') | NO   |     | N       |       | 
| Grant_priv            | enum('N','Y') | NO   |     | N       |       | 
| References_priv       | enum('N','Y') | NO   |     | N       |       | 
| Index_priv            | enum('N','Y') | NO   |     | N       |       | 
| Alter_priv            | enum('N','Y') | NO   |     | N       |       | 
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       | 
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       | 
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       | 
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       | 
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       | 
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       | 
| Execute_priv          | enum('N','Y') | NO   |     | N       |       | 
+-----------------------+---------------+------+-----+---------+-------+

mysql> describe func; 
+-------+------------------------------+------+-----+---------+-------+
| Field | Type                         | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| name  | char(64)                     | NO   | PRI |         |       | 
| ret   | tinyint(1)                   | NO   |     | 0       |       | 
| dl    | char(128)                    | NO   |     |         |       | 
| type  | enum('function','aggregate') | NO   |     |         |       | 
+-------+------------------------------+------+-----+---------+-------+

mysql> describe help_category; 
+--------------------+----------------------+------+-----+---------+-------+
| Field              | Type                 | Null | Key | Default | Extra |
+--------------------+----------------------+------+-----+---------+-------+
| help_category_id   | smallint(5) unsigned | NO   | PRI |         |       | 
| name               | char(64)             | NO   | UNI |         |       | 
| parent_category_id | smallint(5) unsigned | YES  |     | NULL    |       | 
| url                | char(128)            | NO   |     |         |       | 
+--------------------+----------------------+------+-----+---------+-------+

mysql> describe help_keyword; 
+-----------------+------------------+------+-----+---------+-------+
| Field           | Type             | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+-------+
| help_keyword_id | int(10) unsigned | NO   | PRI |         |       | 
| name            | char(64)         | NO   | UNI |         |       | 
+-----------------+------------------+------+-----+---------+-------+

mysql> describe help_relation; 
+-----------------+------------------+------+-----+---------+-------+
| Field           | Type             | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+-------+
| help_topic_id   | int(10) unsigned | NO   | PRI |         |       | 
| help_keyword_id | int(10) unsigned | NO   | PRI |         |       | 
+-----------------+------------------+------+-----+---------+-------+

mysql> describe help_topic; 
+------------------+----------------------+------+-----+---------+-------+
| Field            | Type                 | Null | Key | Default | Extra |
+------------------+----------------------+------+-----+---------+-------+
| help_topic_id    | int(10) unsigned     | NO   | PRI |         |       | 
| name             | char(64)             | NO   | UNI |         |       | 
| help_category_id | smallint(5) unsigned | NO   |     |         |       | 
| description      | text                 | NO   |     |         |       | 
| example          | text                 | NO   |     |         |       | 
| url              | char(128)            | NO   |     |         |       | 
+------------------+----------------------+------+-----+---------+-------+

mysql> describe host; 
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       | 
| Db                    | char(64)      | NO   | PRI |         |       | 
| Select_priv           | enum('N','Y') | NO   |     | N       |       | 
| Insert_priv           | enum('N','Y') | NO   |     | N       |       | 
| Update_priv           | enum('N','Y') | NO   |     | N       |       | 
| Delete_priv           | enum('N','Y') | NO   |     | N       |       | 
| Create_priv           | enum('N','Y') | NO   |     | N       |       | 
| Drop_priv             | enum('N','Y') | NO   |     | N       |       | 
| Grant_priv            | enum('N','Y') | NO   |     | N       |       | 
| References_priv       | enum('N','Y') | NO   |     | N       |       | 
| Index_priv            | enum('N','Y') | NO   |     | N       |       | 
| Alter_priv            | enum('N','Y') | NO   |     | N       |       | 
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       | 
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       | 
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       | 
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       | 
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       | 
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       | 
| Execute_priv          | enum('N','Y') | NO   |     | N       |       | 
+-----------------------+---------------+------+-----+---------+-------+

mysql> describe proc; 
+------------------+--------------------------------------------+------+-----+---------------------+-------+
| Field            | Type                                       | Null | Key | Default             | Extra |
+------------------+--------------------------------------------+------+-----+---------------------+-------+
| db               | char(64)                                   | NO   | PRI |                     |       | 
| name             | char(64)                                   | NO   | PRI |                     |       | 
| type             | enum('FUNCTION','PROCEDURE')               | NO   | PRI |                     |       | 
| specific_name    | char(64)                                   | NO   |     |                     |       | 
| language         | enum('SQL')                                | NO   |     | SQL                 |       | 
| sql_data_access  | enum('CONTAINS_SQL','NO_SQL',...)          | NO   |     | CONTAINS_SQL        |       | 
| is_deterministic | enum('YES','NO')                           | NO   |     | NO                  |       | 
| security_type    | enum('INVOKER','DEFINER')                  | NO   |     | DEFINER             |       | 
| param_list       | blob                                       | NO   |     |                     |       | 
| returns          | char(64)                                   | NO   |     |                     |       | 
| body             | longblob                                   | NO   |     |                     |       | 
| definer          | char(77)                                   | NO   |     |                     |       | 
| created          | timestamp                                  | NO   |     | CURRENT_TIMESTAMP   |       | 
| modified         | timestamp                                  | NO   |     | 0000-00-00 00:00:00 |       | 
| sql_mode         | set('REAL_AS_FLOAT','PIPES_AS_CONCAT',...) | NO   |     |                     |       | 
| comment          | char(64)                                   | NO   |     |                     |       | 
+------------------+--------------------------------------------+------+-----+---------------------+-------+

mysql> describe procs_priv; 
+--------------+----------------------------------------+------+-----+-------------------+-------+
| Field        | Type                                   | Null | Key | Default           | Extra |
+--------------+----------------------------------------+------+-----+-------------------+-------+
| Host         | char(60)                               | NO   | PRI |                   |       | 
| Db           | char(64)                               | NO   | PRI |                   |       | 
| User         | char(16)                               | NO   | PRI |                   |       | 
| Routine_name | char(64)                               | NO   | PRI |                   |       | 
| Routine_type | enum('FUNCTION','PROCEDURE')           | NO   | PRI |                   |       | 
| Grantor      | char(77)                               | NO   | MUL |                   |       | 
| Proc_priv    | set('Execute','Alter Routine','Grant') | NO   |     |                   |       | 
| Timestamp    | timestamp                              | NO   |     | CURRENT_TIMESTAMP |       | 
+--------------+----------------------------------------+------+-----+-------------------+-------+

mysql> describe tables_priv; 
+-------------+----------------------------+------+-----+-------------------+-------+
| Field       | Type                       | Null | Key | Default           | Extra |
+-------------+----------------------------+------+-----+-------------------+-------+
| Host        | char(60)                   | NO   | PRI |                   |       | 
| Db          | char(64)                   | NO   | PRI |                   |       | 
| User        | char(16)                   | NO   | PRI |                   |       | 
| Table_name  | char(64)                   | NO   | PRI |                   |       | 
| Grantor     | char(77)                   | NO   | MUL |                   |       | 
| Timestamp   | timestamp                  | NO   |     | CURRENT_TIMESTAMP |       | 
| Table_priv  | set('Select','Insert',...) | NO   |     |                   |       | 
| Column_priv | set('Select','Insert',...) | NO   |     |                   |       | 
+-------------+----------------------------+------+-----+-------------------+-------+

mysql> describe time_zone; 
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| Time_zone_id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| Use_leap_seconds | enum('Y','N')    | NO   |     | N       |                | 
+------------------+------------------+------+-----+---------+----------------+

mysql> describe time_zone_leap_second; 
+-----------------+------------+------+-----+---------+-------+
| Field           | Type       | Null | Key | Default | Extra |
+-----------------+------------+------+-----+---------+-------+
| Transition_time | bigint(20) | NO   | PRI |         |       | 
| Correction      | int(11)    | NO   |     |         |       | 
+-----------------+------------+------+-----+---------+-------+

mysql> describe time_zone_name; 
+--------------+------------------+------+-----+---------+-------+
| Field        | Type             | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| Name         | char(64)         | NO   | PRI |         |       | 
| Time_zone_id | int(10) unsigned | NO   |     |         |       | 
+--------------+------------------+------+-----+---------+-------+

mysql> describe time_zone_transition; 
+--------------------+------------------+------+-----+---------+-------+
| Field              | Type             | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+-------+
| Time_zone_id       | int(10) unsigned | NO   | PRI |         |       | 
| Transition_time    | bigint(20)       | NO   | PRI |         |       | 
| Transition_type_id | int(10) unsigned | NO   |     |         |       | 
+--------------------+------------------+------+-----+---------+-------+

mysql> describe time_zone_transition_type; 
+--------------------+---------------------+------+-----+---------+-------+
| Field              | Type                | Null | Key | Default | Extra |
+--------------------+---------------------+------+-----+---------+-------+
| Time_zone_id       | int(10) unsigned    | NO   | PRI |         |       | 
| Transition_type_id | int(10) unsigned    | NO   | PRI |         |       | 
| Offset             | int(11)             | NO   |     | 0       |       | 
| Is_DST             | tinyint(3) unsigned | NO   |     | 0       |       | 
| Abbreviation       | char(8)             | NO   |     |         |       | 
+--------------------+---------------------+------+-----+---------+-------+

mysql> describe user; 
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | char(60)                          | NO   | PRI |         |       | 
| User                  | char(16)                          | NO   | PRI |         |       | 
| Password              | char(41)                          | NO   |     |         |       | 
| Select_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Insert_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Update_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Delete_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Create_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Drop_priv             | enum('N','Y')                     | NO   |     | N       |       | 
| Reload_priv           | enum('N','Y')                     | NO   |     | N       |       | 
| Shutdown_priv         | enum('N','Y')                     | NO   |     | N       |       | 
| Process_priv          | enum('N','Y')                     | NO   |     | N       |       | 
| File_priv             | enum('N','Y')                     | NO   |     | N       |       | 
| Grant_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| References_priv       | enum('N','Y')                     | NO   |     | N       |       | 
| Index_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| Alter_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| Show_db_priv          | enum('N','Y')                     | NO   |     | N       |       | 
| Super_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N       |       | 
| Lock_tables_priv      | enum('N','Y')                     | NO   |     | N       |       | 
| Execute_priv          | enum('N','Y')                     | NO   |     | N       |       | 
| Repl_slave_priv       | enum('N','Y')                     | NO   |     | N       |       | 
| Repl_client_priv      | enum('N','Y')                     | NO   |     | N       |       | 
| Create_view_priv      | enum('N','Y')                     | NO   |     | N       |       | 
| Show_view_priv        | enum('N','Y')                     | NO   |     | N       |       | 
| Create_routine_priv   | enum('N','Y')                     | NO   |     | N       |       | 
| Alter_routine_priv    | enum('N','Y')                     | NO   |     | N       |       | 
| Create_user_priv      | enum('N','Y')                     | NO   |     | N       |       | 
| ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       | 
| ssl_cipher            | blob                              | NO   |     |         |       | 
| x509_issuer           | blob                              | NO   |     |         |       | 
| x509_subject          | blob                              | NO   |     |         |       | 
| max_questions         | int(11) unsigned                  | NO   |     | 0       |       | 
| max_updates           | int(11) unsigned                  | NO   |     | 0       |       | 
| max_connections       | int(11) unsigned                  | NO   |     | 0       |       | 
| max_user_connections  | int(11) unsigned                  | NO   |     | 0       |       | 
+-----------------------+-----------------------------------+------+-----+---------+-------+

test

The test database is a "sandbox" for MySQL users to try things out. By default, it contains no tables.


This wiki page is maintained by Rich Morin, an independent consultant specializing in software design, development, and documentation. Please feel free to email comments, inquiries, suggestions, etc!

Topic revision: r13 - 12 Jun 2008, RichMorin
This site is powered by Foswiki Copyright © by the contributing authors. All material on this wiki is the property of the contributing authors.
Foswiki version v2.1.6, Release Foswiki-2.1.6, Plugin API version 2.4
Ideas, requests, problems regarding CFCL Wiki? Send us email