Databases contain data, but information about the way databases are structured is metadata. The primary
way of obtaining metadata is by using the INFORMATION_SCHEMA database to access metadata. MySQL produces metadata for several aspects of database structure. To name a few, metadata can be obtained from such database information as names of databases and tables, information about columns and indexes in tables, or stored routine definitions.
Database Information
In the world of web based applications. getting information about databases is important to be able to create
generic and scalable applications. This section will demonstrate how to obtain information concerning the
databases, the tables and the columns located in the MySQL server. Due to the fact that the root user is
being used to log into the MySQL server, the resulting queries against the INFORMATION_SCHEMA
database will result in a display of all information contained on the server. However, the majority of users
will have limited information that they will be able to view from the INFORMATION_SCHEMA database.
Only that which a user has access to can be seen. If a user has access to a database, then they will have
access to view the information pertaining to that database.
The Top Three
Even though the INFORMATION_SCHEMA database has multiple tables of data relating to just about
every component of a database, the tables that are the most useful for a PHP programmer are the
SCHEMATA (Information about the databases), TABLES (Information about the tables in the databases)
and COLUMNS (Information about the columns in tables). Understanding and being able to draw
information out of these three tables will answer the majority of questions that could be asked about the
information in the MySQL server.
• SCHEMATA Table - The standard SQL INFORMATION_SCHEMA.SCHEMATA table shows the
schemas that may be accessed by the current user. The following are the most useful columns to view
from this table (the table contains 4 columns in total):
○ SCHEMA_NAME - shows the name of a schema (i.e., a database) which the current
user may use.
○ DEFAULT_CHARACTER_SET_NAME - shows the name of the database's default
character set.
• TABLES Table - The standard SQL INFORMATION_SCHEMA.TABLES table shows the base
tables and views that are available to the current user. The majority of the columns in this table are
MySQL specific columns and are not part of the standard SQL implementation of this table. The
following are the most useful columns to view from this table (there are 21 columns in total):
○ TABLE_SCHEMA - shows the name of the schema (i.e., the database) in which an
available table resides.
○ TABLE_NAME - shows the name of a table which the current user may access (i.e., on
which the current user has been granted a privilege).
○ TABLE_TYPE - shows whether this table is a 'BASE TABLE', a 'TEMPORARY' table,
or a 'VIEW'.
○ ENGINE - shows the storage engine used for this table and is not part of the standard
SQL definition.
○ ROW_FORMAT - shows this table's row storage format; either 'FIXED', 'DYNAMIC', or
'COMPRESSED' and is not part of the standard SQL definition.
○ TABLE_ROWS - shows the number of rows in this table and is not part of the standard
SQL definition.
○ INDEX_LENGTH - shows the length of the index file associated with this table and is
not part of the standard SQL definition.
○ AUTO_INCREMENT - shows the next AUTO_INCREMENT value where applicable;
otherwise NULL. This column is not part of the standard SQL definition.
○ CREATE_TIME - shows the timestamp of the time this table was created and is not part
of the standard SQL definition.
○ UPDATE_TIME - shows the timestamp of the time this table's data file was last updated
and is not part of the standard SQL definition.
○ CHECK_TIME - shows the timestamp of the time this table was last checked; NULL if
the table has never been checked. This column is not part of the standard SQL definition.
○ TABLE_COLLATION - shows this table's default character set and collation
combination and is not part of the standard SQL definition.
○ CREATE_OPTIONS - shows any additional options used in the table's definition;
otherwise left blank. This column is not part of the standard SQL definition.
○ TABLE_COMMENT - shows the comment, if any, stored for this table; otherwise left
blank. This column is not part of the standard SQL definition.
• COLUMNS Table - The standard SQL INFORMATION_SCHEMA.COLUMNS table shows the
columns that are available to the current user. Similar to the TABLES table, there are number of
columns in this table that are MySQL specific columns and are not part of the standard SQL
implementation. The following are the most useful columns to view from this table (there are 19
columns in total):
○ TABLE_SCHEMA - shows the name of the schema (i.e., the database) in which the table
that contains an available column resides.
○ TABLE_NAME - shows the name of the table to which an available column belongs.
○ COLUMN_NAME - shows the name of a column that may be accessed by the current
user (i.e., for which the current user has been granted a privilege).
○ ORDINAL_POSITION - shows the ordinal position of the column in the table to which it
belongs.
○ COLUMN_DEFAULT - shows the column's default value. If this column is blank, the
column has no defined default value.
○ IS_NULLABLE - shows whether the column may accept NULL values; either 'YES' or
'NO'.
○ DATA_TYPE - shows the column's defined data type (keyword only, not the entire
definition).
○ CHARACTER_SET_NAME - shows, for a column with a character string data type, the
column's default character set; otherwise NULL.
○ COLLATION_NAME - shows, for a column with a character string data type, the
column's default collation; otherwise NULL.
○ COLUMN_TYPE - shows the column's defined data type in full and is not part of the
standard SQL definition.
○ COLUMN_KEY - shows whether the column is indexed; either 'PRI' if the column is
part of a PRIMARY KEY, 'UNI' if the column is part of a UNIQUE key, 'MUL' if the
column is part of an index key that allows duplicates, or blank if the column is not
indexed.
○ EXTRA - shows any additional column definition information, e.g., whether the column
was defined with the AUTO_INCREMENT attribute; otherwise left blank. This is not
part of the standard SQL definition.
○ PRIVILEGES - shows the privileges available to the current user on the column and is
not part of the standard SQL definition.
way of obtaining metadata is by using the INFORMATION_SCHEMA database to access metadata. MySQL produces metadata for several aspects of database structure. To name a few, metadata can be obtained from such database information as names of databases and tables, information about columns and indexes in tables, or stored routine definitions.
Database Information
In the world of web based applications. getting information about databases is important to be able to create
generic and scalable applications. This section will demonstrate how to obtain information concerning the
databases, the tables and the columns located in the MySQL server. Due to the fact that the root user is
being used to log into the MySQL server, the resulting queries against the INFORMATION_SCHEMA
database will result in a display of all information contained on the server. However, the majority of users
will have limited information that they will be able to view from the INFORMATION_SCHEMA database.
Only that which a user has access to can be seen. If a user has access to a database, then they will have
access to view the information pertaining to that database.
The Top Three
Even though the INFORMATION_SCHEMA database has multiple tables of data relating to just about
every component of a database, the tables that are the most useful for a PHP programmer are the
SCHEMATA (Information about the databases), TABLES (Information about the tables in the databases)
and COLUMNS (Information about the columns in tables). Understanding and being able to draw
information out of these three tables will answer the majority of questions that could be asked about the
information in the MySQL server.
• SCHEMATA Table - The standard SQL INFORMATION_SCHEMA.SCHEMATA table shows the
schemas that may be accessed by the current user. The following are the most useful columns to view
from this table (the table contains 4 columns in total):
○ SCHEMA_NAME - shows the name of a schema (i.e., a database) which the current
user may use.
○ DEFAULT_CHARACTER_SET_NAME - shows the name of the database's default
character set.
• TABLES Table - The standard SQL INFORMATION_SCHEMA.TABLES table shows the base
tables and views that are available to the current user. The majority of the columns in this table are
MySQL specific columns and are not part of the standard SQL implementation of this table. The
following are the most useful columns to view from this table (there are 21 columns in total):
○ TABLE_SCHEMA - shows the name of the schema (i.e., the database) in which an
available table resides.
○ TABLE_NAME - shows the name of a table which the current user may access (i.e., on
which the current user has been granted a privilege).
○ TABLE_TYPE - shows whether this table is a 'BASE TABLE', a 'TEMPORARY' table,
or a 'VIEW'.
○ ENGINE - shows the storage engine used for this table and is not part of the standard
SQL definition.
○ ROW_FORMAT - shows this table's row storage format; either 'FIXED', 'DYNAMIC', or
'COMPRESSED' and is not part of the standard SQL definition.
○ TABLE_ROWS - shows the number of rows in this table and is not part of the standard
SQL definition.
○ INDEX_LENGTH - shows the length of the index file associated with this table and is
not part of the standard SQL definition.
○ AUTO_INCREMENT - shows the next AUTO_INCREMENT value where applicable;
otherwise NULL. This column is not part of the standard SQL definition.
○ CREATE_TIME - shows the timestamp of the time this table was created and is not part
of the standard SQL definition.
○ UPDATE_TIME - shows the timestamp of the time this table's data file was last updated
and is not part of the standard SQL definition.
○ CHECK_TIME - shows the timestamp of the time this table was last checked; NULL if
the table has never been checked. This column is not part of the standard SQL definition.
○ TABLE_COLLATION - shows this table's default character set and collation
combination and is not part of the standard SQL definition.
○ CREATE_OPTIONS - shows any additional options used in the table's definition;
otherwise left blank. This column is not part of the standard SQL definition.
○ TABLE_COMMENT - shows the comment, if any, stored for this table; otherwise left
blank. This column is not part of the standard SQL definition.
• COLUMNS Table - The standard SQL INFORMATION_SCHEMA.COLUMNS table shows the
columns that are available to the current user. Similar to the TABLES table, there are number of
columns in this table that are MySQL specific columns and are not part of the standard SQL
implementation. The following are the most useful columns to view from this table (there are 19
columns in total):
○ TABLE_SCHEMA - shows the name of the schema (i.e., the database) in which the table
that contains an available column resides.
○ TABLE_NAME - shows the name of the table to which an available column belongs.
○ COLUMN_NAME - shows the name of a column that may be accessed by the current
user (i.e., for which the current user has been granted a privilege).
○ ORDINAL_POSITION - shows the ordinal position of the column in the table to which it
belongs.
○ COLUMN_DEFAULT - shows the column's default value. If this column is blank, the
column has no defined default value.
○ IS_NULLABLE - shows whether the column may accept NULL values; either 'YES' or
'NO'.
○ DATA_TYPE - shows the column's defined data type (keyword only, not the entire
definition).
○ CHARACTER_SET_NAME - shows, for a column with a character string data type, the
column's default character set; otherwise NULL.
○ COLLATION_NAME - shows, for a column with a character string data type, the
column's default collation; otherwise NULL.
○ COLUMN_TYPE - shows the column's defined data type in full and is not part of the
standard SQL definition.
○ COLUMN_KEY - shows whether the column is indexed; either 'PRI' if the column is
part of a PRIMARY KEY, 'UNI' if the column is part of a UNIQUE key, 'MUL' if the
column is part of an index key that allows duplicates, or blank if the column is not
indexed.
○ EXTRA - shows any additional column definition information, e.g., whether the column
was defined with the AUTO_INCREMENT attribute; otherwise left blank. This is not
part of the standard SQL definition.
○ PRIVILEGES - shows the privileges available to the current user on the column and is
not part of the standard SQL definition.
Comments
Post a Comment