12.Obtaining Metadata in MySql

 

Index : 1. Available metadata access methods (2). Structures of information_ schema (3). Using available commands to view metadata (4).Differences between SHOW statements and INFORMATION_SCHEMA tables (5).The MYSQL show client program (6).Using INFORMATION_SCHEMA queries to create shell commands and SQL statements

Metadata:

A database is structured collection of data but Metadata is just like index about the data MySql provides the facility  access to metadata for database , table and other object managed by the database server.

INFORMATION_SCHEMA is database default lunch with mysql instance,It is stores information
about all the other databases .It is store only read only tables.but it is actually views not base tables ,so there are no files associated with them , and you cannot set triggers on them. If you can use INFORMATION_SCHEMA as the default  database , you can only read the contens of tables not perform INSERT UPDATE etc operations .

SELECT   TABLE_NAME
 FROM     INFORMATION_SCHEMA.TABLES
     WHERE    TABLE_SCHEMA = 'information_schema'
     ORDER BY TABLE_NAME;

Table Information

• COLUMNS : Columns in tables and views

• ENGINES : Storage engines
• SCHEMATA: Databases
• TABLES :Tables in databases
• VIEWS :Views in databases

Partitioning

• PARTITIONS: Table partitions

• FILES: Files in which MySQL NDB disk data tables are stored

Privileges

• COLUMN_PRIVILEGES: Column privileges held by MySQL user accounts

• SCHEMA_PRIVILEGES: Database privileges held by MySQL user accounts
• TABLE_PRIVILEGES: Table privileges held by MySQL user accounts
• USER_PRIVILEGES: Global privileges held by MySQL user accounts

Character Set Support

• CHARACTER_SETS: Available character sets

• COLLATIONS: Collations for each character set
• COLLATION_CHARACTER_SET_APPLICABILITY: Collations that are applicable to a particular character set

Constraints and Indexes

• KEY_COLUMN_USAGE: Constraints on key columns

• REFERENTIAL_CONSTRAINTS: Foreign keys
• STATISTICS: Table indexes
• TABLE_CONSTRAINTS: Constraints on tables

Server Settings and Status

• KEY_COLUMN_USAGE: Constraints

• GLOBAL_STATUS: The status values for all connections to MySQL
• GLOBAL_VARIABLES: The values that are used for new connections to MySQL
• PLUGINS: Server plugins
• ROCESSLIST: Indicates which threads are running
• SESSION_STATUS: The status values for the current connection to MySQL
• SESSION_VARIABLES: The values that are in effect for the current connection to MySQL

Routines and Related Information

• EVENTS: Scheduled events

• ROUTINES: Stored procedures and functions
• TRIGGERS: Triggers in databases
• PARAMETERS: Stored procedure and function parameters, and stored functions

InnoDB

• INNODB_CMPand INNODB_CMP_RESET: Status on operations related to compressed InnoDB tables

• INNODB_CMPMEMand INNODB_CMPMEM_RESET: Status on compressed pages within the InnoDB buffer pool
• INNODB_LOCKS: Each lock that an InnoDB transaction has requested and holds
• INNODB_LOCK_WAITS: One or more row locks for each blocked InnoDB transaction
• INNODB_TRX: Every transaction currently executing inside InnoDB
• TABLESPACES: Active tablespaces

MySql Collection : is set of rules which are compare character in character set .

#SHOW COLLATION

# Collation, Charset, Id, Default, Compiled, Sortlen, Pad_attribute
   'armscii8_bin', 'armscii8', '64', '', 'Yes', '1', 'PAD SPACE'
    'armscii8_general_ci', 'armscii8', '32', 'Yes', 'Yes', '1', 'PAD SPACE'
    'ascii_bin', 'ascii', '65', '', 'Yes', '1', 'PAD SPACE'
    'ascii_general_ci', 'ascii', '11', 'Yes', 'Yes', '1', 'PAD SPACE'
    'big5_bin', 'big5', '84', '', 'Yes', '1', 'PAD SPACE'
    'big5_chinese_ci', 'big5', '1', 'Yes', 'Yes', '1', 'PAD SPACE'

Character set: is set of symbols and encoding techniques and it is set of rules for comparing character in Character set It is compare according to variety of collections.

SHOW CHARACTER SET like  '%utf16%' ;

# Charset, Description, Default collation, Maxlen
    'utf16', 'UTF-16 Unicode', 'utf16_general_ci', '4'
    'utf16le', 'UTF-16LE Unicode', 'utf16le_general_ci', '4'

Examples :

In latin1 each character is exactly one byte long. In utf8 a character can consist of more than one byte. Consequently utf8 has more characters than latin1 .

# Charset, Description, Default collation, Maxlen
    'latin1', 'cp1252 West European', 'latin1_swedish_ci', '1'
 

# How to set character set

Example:

MYSQL>CREATE DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8_unicode_ci; 

# How to Change character set

MYSQL> ALTER DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

SHOW & DESCRIBE SYNTEX : 

    1.SHOW DATABASES: Lists the names of the available databases  

    2.SHOW TABLES: Lists the tables in the default database  

    3.SHOW TABLES FROM [database_name]: Lists the tables in the                      specified database 

    4.SHOW COLUMNS FROM [table_name]: Displays column structure for                the table

    5.SHOW INDEX FROM [table_name]: Displays information about the                     indexes and index columns inthe table 

    6.SHOW CHARACTER SET: Displays the available character sets along                  with their default collations 

    7.SHOW COLLATION: Displays the collations for each character set

 

mysql> SHOW DATABASES; 
+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| mysql              | 
| test               | 
| world_innodb       | 
+--------------------+

MySQL> SHOW TABLES; 
MySQL> SHOW TABLES FROM mysql; 
MySQL> SHOW TABLES FROM INFORMATION_SCHEMA; 
MySQL> SHOW COLUMNS FROM CountryLanguage; 
MySQL> SHOW FULL COLUMNS FROM CountryLanguage\G

Additional SHOW Statement Examples

1. SHOW with LIKE and WHERE:

MySQL> SHOW DATABASES LIKE 'm%'; 
MySQL> SHOW COLUMNS FROM Country 
       WHERE `Default` IS NULL; 

2. Other SHOW statements:

MySQL> SHOW INDEX FROM City\G 
MySQL> SHOW CHARACTER SET; 
MySQL> SHOW COLLATION;

DESCRIBE

DESCRIBEcan be abbreviated as DESC, as follows;

mysql> DESCRIBE table_name; 
and 
mysql> DESC table_name; 

Example below shows INFORMATION_SCHEMA table information

MySQL> DESCRIBE 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       |       | 
+----------------------+-------------+------+-----+---------+-------+

The following is equivalent to the above DESCRIBE/DESC examples:

mysql> SHOW COLUMNS FROM table_name; 

mysqlshow: Examples

You can show information for all databases, or for a specific database, table, and/or column using the mysqlshow query. For example:

shell> mysqlshow -u -p 
+--------------------+ 
|     Databases      | 
+--------------------+ 
| information_schema | 
| mysql              | 
| performance_schema | 
| test               | 
| world_innodb       | 
+--------------------+

shell> mysqlshow world_innodb 
shell> mysqlshow world_innodb City 
shell> mysqlshow world_innodb City CountryCode 
shell> mysqlshow "w%"