MySQL SHOW COMMANDS
SHOW COMMANDS
There are various forms of MySQL SHOW commands, which provides information about databases, tables, columns, or status information about the commands. See the following section:
Version: MySQL 5.6
Table of contents
MySQL: SHOW BINARY LOGS
SHOW BINARY LOGS statement is used to list the binary log files on the server. Here is the syntax:
SHOW BINARY LOGS
See the following examples:
mysql> SHOW BINARY LOGS; +---------------+-----------+ | Log_name | File_size | +---------------+-----------+ | binlog.000015 | 724935 | | binlog.000016 | 733481 | +---------------+-----------+
MySQL: SHOW BINLOG EVENTS
SHOW BINLOG EVENTS statement shows the events in the binary log. Here is the syntax:
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
If you omit 'log_name', the first binary log is displayed.
The LIMIT clause can be used to constrain the number of rows returned by the statement.
MySQL: SHOW CHARACTER SET
SHOW CHARACTER SET statement is used to check all available character sets. Here is the syntax :
SHOW CHARACTER SET
[LIKE 'pattern' | WHERE expr]
The optional LIKE clause, if present, shows the matched character set. With WHERE clause you can use a condition.
See the following examples :
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 | ........ 40 rows in set (0.03 sec)
Let see all the character set starting with 'utf"
mysql> SHOW CHARACTER SET LIKE 'utf%'; +---------+------------------+--------------------+--------+ | Charset | Description | Default collation | Maxlen | +---------+------------------+--------------------+--------+ | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 | | utf32 | UTF-32 Unicode | utf32_general_ci | 4 | +---------+------------------+--------------------+--------+ 5 rows in set (0.00 sec)
Let see all the character set starting with 'utf" and Maxlen is 3:
mysql> SHOW CHARACTER SET WHERE Maxlen=3; +---------+---------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +---------+---------------------------+---------------------+--------+ | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | +---------+---------------------------+---------------------+--------+ 3 rows in set (0.00 sec)
MySQL: SHOW COLLATION
SHOW COLLATION statement is used to list collations (a collation is a set of rules for comparing characters in a character set) supported by the server. Here is the syntax:
SHOW COLLATION SET
[LIKE 'pattern' | WHERE expr]
The optional LIKE clause, if present, shows the matched collations.
With WHERE clause you can attach a condition.
See the following examples:
mysql> SHOW COLLATION; +--------------------------+----------+-----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +--------------------------+----------+-----+---------+----------+---------+ | big5_chinese_ci | big5 | 1 | Yes | Yes | 1 | | big5_bin | big5 | 84 | | Yes | 1 | | dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 | | dec8_bin | dec8 | 69 | | Yes | 1 | | cp850_general_ci | cp850 | 4 | Yes | Yes | 1 | | cp850_bin | cp850 | 80 | | Yes | 1 | | hp8_english_ci | hp8 | 6 | Yes | Yes | 1 | | hp8_bin | hp8 | 72 | | Yes | 1 | | koi8r_general_ci | koi8r | 7 | Yes | Yes | 1 | | koi8r_bin | koi8r | 74 | | Yes | 1 | | latin1_german1_ci | latin1 | 5 | | Yes | 1 | ........ 219 rows in set (0.06 sec)
Following command shows collation like utf:
mysql> SHOW COLLATION LIKE 'utf%'; +--------------------------+---------+-----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +--------------------------+---------+-----+---------+----------+---------+ | utf8_general_ci | utf8 | 33 | Yes | Yes | 1 | | utf8_bin | utf8 | 83 | | Yes | 1 | | utf8_unicode_ci | utf8 | 192 | | Yes | 8 | | utf8_icelandic_ci | utf8 | 193 | | Yes | 8 | | utf8_latvian_ci | utf8 | 194 | | Yes | 8 | | utf8_romanian_ci | utf8 | 195 | | Yes | 8 | | utf8_slovenian_ci | utf8 | 196 | | Yes | 8 | | utf8_polish_ci | utf8 | 197 | | Yes | 8 | | utf8_estonian_ci | utf8 | 198 | | Yes | 8 | | utf8_spanish_ci | utf8 | 199 | | Yes | 8 | | utf8_swedish_ci | utf8 | 200 | | Yes | 8 | ........ 107 rows in set (0.00 sec)
MySQL: SHOW COLUMNS
The SHOW COLUMNS statement is used to display information about the columns in a given table. Here is the syntax:
SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
The optional LIKE clause, if present shows the matched column names. With WHERE clause you can use a condition.
See the following examples:
mysql> SHOW COLUMNS FROM user_details; +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | userid | varchar(16) | NO | PRI | NULL | | | password | varchar(16) | NO | | NULL | | | fname | varchar(100) | NO | | NULL | | | lname | varchar(100) | NO | | NULL | | | gender | varchar(1) | NO | | NULL | | | dtob | date | NO | | NULL | | | country | varchar(30) | NO | | NULL | | | user_rating | int(4) | NO | | NULL | | | emailid | varchar(60) | NO | | NULL | | +-------------+--------------+------+-----+---------+-------+ 9 rows in set (0.25 sec)
MySQL: SHOW CREATE DATABASE
SHOW CREATE DATABASE statement is used to show CREATE DATABASE statement.
SHOW CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
See the following example:
mysql> SHOW CREATE DATABASE hr; +----------+-------------------------------------------------------------+ | Database | Create Database | +----------+-------------------------------------------------------------+ | hr | CREATE DATABASE `hr` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+-------------------------------------------------------------+ 1 row in set (0.00 sec)
MySQL : SHOW CREATE EVENT
This statement displays the CREATE EVENT statement needed to re-create a given event. It requires the EVENT privilege for the database from which the event is to be shown.
SHOW CREATE EVENT event_name
MySQL : SHOW CREATE FUNCTION
SHOW CREATE FUNCTION statement is used to get the exact string that can be used to re-create the named stored function.
Here is the syntax:
SHOW CREATE FUNCTION func_name
Here is the statement to create a function 'test1'
mysql> CREATE FUNCTION test1 (aa CHAR(25))
-> RETURNS CHAR(50) DETERMINISTIC
-> RETURN CONCAT('w3resource.',aa,' sites');
-> //
Query OK, 0 rows affected (0.00 sec)
Here is the following statement of SHOW CREATE FUNCTION.
SHOW CREATE FUNCTION test1\G
Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output:
Sample Output:
mysql> SHOW CREATE FUNCTION test1\G
*************************** 1. row ***************************
Function: test1
sql_mode:
Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `test1`(aa CHAR(25)) RETURNS char(50) CHARSET latin1
DETERMINISTIC
RETURN CONCAT('w3resource.',aa,' sites')
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
MySQL: SHOW CREATE PROCEDURE
SHOW CREATE PROCEDURE statement is used to get the exact string that can be used to re-create the named stored procedure. The statement requires that you must be the owner of the routine.
Here is the syntax:
SHOW CREATE PROCEDURE proc_name
Here is the statement to create a procedure 'myprocedure'
mysql> CREATE PROCEDURE myprocedure (OUT emp_ctr INT)
-> BEGIN
-> SELECT COUNT(*) INTO emp_ctr FROM empinfo.employees;
-> END//
Query OK, 0 rows affected (0.00 sec)
Here is the following statement of SHOW CREATE PROCEDURE.
SHOW CREATE PROCEDURE empinfo.myprocedure\G
Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output:
Sample Output:
mysql> SHOW CREATE PROCEDURE empinfo.myprocedure\G
*************************** 1. row ***************************
Procedure: myprocedure
sql_mode:
Create Procedure: CREATE DEFINER=`root`@`localhost`
PROCEDURE `myprocedure`(OUT emp_ctr INT)
BEGIN
SELECT COUNT(*) INTO emp_ctr FROM empinfo.employees;
END
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
MySQL: SHOW CREATE TABLE
SHOW CREATE TABLE statement is used to show the create table statement.
Here is the syntax:
SHOW CREATE TABLE table_name;
See the following example:
SHOW CREATE TABLE regions\G;
Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output:
Sample Output:
mysql> SHOW CREATE TABLE regions\G
*************************** 1. row ***************************
Table: regions
Create Table: CREATE TABLE `regions` (
`REGION_ID` decimal(5,0) NOT NULL,
`REGION_NAME` varchar(25) DEFAULT NULL,
PRIMARY KEY (`REGION_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
MySQL: SHOW CREATE TRIGGER
SHOW CREATE TRIGGERS statement is used to show the CREATE TRIGGER statement that creates the named trigger.
Here is the syntax:
SHOW CREATE TRIGGER trigger_name
See the following example:
mysql> SHOW CREATE TRIGGER ins_sum\G;
*************************** 1. row ***************************
Trigger: ins_sum
sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`::1` TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.02 sec)
ERROR:
No query specified
MySQL: SHOW CREATE VIEW
SHOW CREATE VIEW statement is used to show the create view statement.
Here is the syntax :
SHOW CREATE VIEW view_name;
See the following example:
SHOW CREATE VIEW myview\G;
Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output:
Sample Output:
mysql> SHOW CREATE VIEW MYVIEW\G;
*************************** 1. row ***************************
View: myview
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`
SQL SECURITY DEFINER VIEW `myview` AS
select `departments`.`DEPARTMENT_ID` AS `DEPARTMENT_ID`,
`departments`.`DEPARTMENT_NAME` AS `DEPARTMENT_NAME`,
`departments`.`MANAGER_ID` AS `MANAGER_ID`,
`departments`.`LOCATION_ID` AS `LOCATION_ID`
from `departments`
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set (0.13 sec)
MySQL: SHOW DATABASES
SHOW DATABASES statement is used to lists the databases on the MySQL server host. The SHOW SCHEMAS can be used as a synonym for SHOW DATABASES.
Here is the syntax :
SHOW {DATABASES | SCHEMAS}
[LIKE 'pattern' | WHERE expr]
If the LIKE clause is present along with the SHOW DATABASES, indicates which database names to match.
See the following example:
SHOW DATABASES;
Let execute the above and see the output:
Sample Output:
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | bookinfo | | bupayroll | | bupf | | empinfo | | mucpf | | mucstuinfo | | mysql | +--------------------+ 8 rows in set (0.16 sec)
Here is the alternate statement of SHOW DATABASES:
mysql> SHOW SCHEMAS; +--------------------+ | Database | +--------------------+ | information_schema | | bookinfo | | bupayroll | | bupf | | empinfo | | mucpf | | mucstuinfo | | mysql | +--------------------+ 8 rows in set (0.02 sec)
Here is the example of SHOW DATABASES using LIKE.
mysql> SHOW DATABASES LIKE 'm%'; +---------------+ | Database (m%) | +---------------+ | mucpf | | mucstuinfo | | mysql | +---------------+ 3 rows in set (0.03 sec)
MySQL : SHOW ENGINE
The SHOW ENGINE statement is used to display operational information about a storage engine.
Here is the syntax:
SHOW ENGINE INNODB STATUS
SHOW ENGINE INNODB MUTEX
SHOW ENGINE {NDB | NDBCLUSTER} STATUS
SHOW ENGINE PERFORMANCE_SCHEMA STATUS
See the following example:
SHOW ENGINE INNODB STATUS\G;
Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output:
Sample Output:
mysql> SHOW ENGINE INNODB STATUS\G; *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 130729 18:26:13 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 11 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 3, signal count 3 Mutex spin waits 0, rounds 0, OS waits 0 RW-shared spins 4, OS waits 2; RW-excl spins 1, OS waits 1 ------------ TRANSACTIONS ------------ Trx id counter 0 1792 Purge done for trx's n:o < 0 0 undo n:o < 0 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 3512 MySQL thread id 1, query id 16 localhost 127.0.0.1 root SHOW ENGINE INNODB STATUS -------- FILE I/O -------- I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 29 OS file reads, 3 OS file writes, 3 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 0 46419 Log flushed up to 0 46419 Last checkpoint at 0 46419 0 pending log writes, 0 pending chkp writes 8 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 14857048; in additional pool allocated 857856 Dictionary memory allocated 20024 Buffer pool size 512 Free buffers 493 Database pages 19 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 19, created 0, written 0 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread id 1032, state: waiting for server activity Number of rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.00 sec)
MySQL: SHOW ENGINES
The SHOW ENGINES statement is used to display the status information about the server's storage engines. It is important for checking whether a storage engine is supported, or what the default engine is.
Here is the syntax:
SHOW [STORAGE] ENGINES;
See the following example:
SHOW ENGINES\G
Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output :
Sample Output:
mysql> SHOW ENGINES\G
*************************** 1. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 3. row ***************************
Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: InnoDB
Support: YES
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
8 rows in set (0.00 sec)
MySQL : SHOW ERRORS
The SHOW ERRORS statement is used to display the errors, warnings, and notes. This statement is almost similar to SHOW WARNINGS except displaying errors.
Here is the syntax:
SHOW ERRORS [LIMIT [offset,] row_count] SHOW COUNT(*) ERRORS
The LIMIT clause can be used to specify the number of rows to be retrieved.
The offset is an argument, which LIMIT takes optionally to retrieve the number of rows. When mention two arguments, the first one is from a particular position and the second one is a number of rows after the first one position.
The offset of the initial row is 0 (not 1)
The SHOW COUNT(*) ERRORS statement is used to displays the number of errors.
See the following example:
Here, in the below statement, there is an error. Execute this statement an error message will be generated.
SHOW DATABASE;
and now, here is the statement-
SHOW ERRORS\G
Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output:
Sample Output:
mysql> SHOW ERRORS\G *************************** 1. row *************************** Level: Error Code: 1064 Message: 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 'database' at line 1 1 row in set (0.02 sec)
SHOW COUNT(*) ERRORS;
Let execute the above and see the output:
Sample Output:
mysql> SHOW COUNT(*) ERRORS; +-----------------------+ | @@session.error_count | +-----------------------+ | 1 | +-----------------------+ 1 row in set (0.11 sec)
Number of errors can also be retrieve using the error_count variable in a SELECT statement.
SELECT @@error_count;
Let execute the above and see the output:
Sample Output:
mysql> SELECT @@error_count; +---------------+ | @@error_count | +---------------+ | 1 | +---------------+ 1 row in set (0.06 sec)
MySQL: SHOW EVENTS
The SHOW EVENTS statement is used to display information about Event Manager events. It requires the EVENT privilege for the database from which the events are to be shown.
Here is the syntax:
SHOW EVENTS [{FROM | IN} schema_name]
[LIKE 'pattern' | WHERE expr]
MySQL: SHOW FUNCTION CODE
This statement is similar to SHOW PROCEDURE CODE but for stored functions.
MySQL: SHOW FUNCTION STATUS
This SHOW FUNCTION STATUS statement returns the characteristics of a stored function, such as the database, name, type, creator, creation and modification dates, and character set information.
Here is the syntax:
SHOW FUNCTION STATUS
[LIKE 'pattern' | WHERE expr]
See the following example.
SHOW FUNCTION STATUS\G
Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output :
Sample Output:
mysql> SHOW FUNCTION STATUS\G
*************************** 1. row ***************************
Db: empinfo
Name: test1
Type: FUNCTION
Definer: root@localhost
Modified: 2013-07-31 17:03:05
Created: 2013-07-31 17:03:05
Security_type: DEFINER
Comment:
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
Let execute the example below using pattern matching and see the output:
Sample Output:
mysql> SHOW FUNCTION STATUS like 'tes%'\G
*************************** 1. row ***************************
Db: empinfo
Name: test1
Type: FUNCTION
Definer: root@localhost
Modified: 2013-07-31 17:03:05
Created: 2013-07-31 17:03:05
Security_type: DEFINER
Comment:
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
Let execute the example below using where and see the output:
Sample Output:
mysql> SHOW FUNCTION STATUS WHERE Db = 'empinfo'\G
*************************** 1. row ***************************
Db: empinfo
Name: test1
Type: FUNCTION
Definer: root@localhost
Modified: 2013-07-31 17:03:05
Created: 2013-07-31 17:03:05
Security_type: DEFINER
Comment:
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
MySQL: SHOW GRANTS
The SHOW GRANTS statement is used to list the GRANT statement or statements that must be issued to duplicate the privileges that are granted to a MySQL user account. The account is named using the same format as for the GRANT statement; If you specify only the username part of the account name, a host name part of '%' is used.
Here is the syntax:
SHOW GRANTS [FOR user]
See the following example.
SHOW GRANTS FOR 'root'@'localhost';
Let execute the above and see the output:
Sample Output:
+---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ 1 row in set (0.03 sec)
Here is the another example of not using the hostname
mysql> SHOW GRANTS FOR 'root'; +-------------------------------------------------------------+ | Grants for root@% | +-------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION | +-------------------------------------------------------------+ 1 row in set (0.00 sec)
In the above example, only the username have specified as the part of the account name that is why in host name part a '%' have appeared.
MySQL : SHOW INDEX
The SHOW INDEX statement returns the information of index of a table.
Here is the syntax :
SHOW {INDEX | INDEXES | KEYS}
{FROM | IN} tbl_name
[{FROM | IN} db_name]
[WHERE expr]
See the following example.
SHOW INDEX FROM employees;
Let execute the above and see the output:
Sample Output:
mysql> SHOW INDEX FROM employees; +-----------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-----------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ | employees | 0 | PRIMARY | 1 | EMPLOYEE_ID | A | 107 | NULL | NULL | | BTREE | | | employees | 0 | EMP_EMAIL_UK | 1 | EMAIL | A | 107 | NULL | NULL | | BTREE | | | employees | 1 | EMP_DEPARTMENT_IX | 1 | DEPARTMENT_ID | A | 11 | NULL | NULL | YES | BTREE | | | employees | 1 | EMP_JOB_IX | 1 | JOB_ID | A | 17 | NULL | NULL | | BTREE | | | employees | 1 | EMP_MANAGER_IX | 1 | MANAGER_ID | A | 17 | NULL | NULL | YES | BTREE | | | employees | 1 | EMP_NAME_IX | 1 | LAST_NAME | A | 107 | NULL | NULL | | BTREE | | | employees | 1 | EMP_NAME_IX | 2 | FIRST_NAME | A | 107 | NULL | NULL | YES | BTREE | | +-----------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ 7 rows in set (0.00 sec)
SHOW KEYS IN empinfo.employees;
Here is another example of SHOW INDEX statement using where clause
mysql> SHOW INDEX FROM employees
-> FROM empinfo WHERE column_name='employee_id';
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| employees | 0 | PRIMARY | 1 | EMPLOYEE_ID | A | 107 | NULL | NULL | | BTREE | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
Here are alternate statements for the above output
SHOW INDEX
FROM empinfo.employees
WHERE column_name='employee_id';
SHOW KEYS
FROM empinfo.employees
WHERE column_name='employee_id';
SHOW KEYS
IN employees
WHERE column_name='employee_id';
MySQL: SHOW MASTER STATUS
The SHOW MASTER STATUS statement provides status information about the binary log files of the master. It requires either the SUPER or REPLICATION CLIENT privilege.
Here is the syntax :
SHOW MASTER STATUS
See the following example.
SHOW MASTER STATUS\G
Let execute the above and see the output:
Sample Output:
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000129
Position: 106
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.03 sec)
Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
MySQL: SHOW OPEN TABLES
SHOW OPEN TABLES statement is used to list the non-TEMPORARY tables that are currently open in the table cache. Here is the syntax :
SHOW OPEN TABLES [{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
See the following example.
SHOW OPEN TABLES FROM empinfo;
Let execute the above and see the output:
Sample Output:
mysql> SHOW OPEN TABLES FROM empinfo; +-----------+-------------+--------+-------------+ | Database | Table | In_use | Name_locked | +-----------+-------------+--------+-------------+ | empinfo | employees | 0 | 0 | | empinfo | departments | 0 | 0 | +-----------+-------------+--------+-------------+ 2 rows in set (0.02 sec)
The SHOW OPEN TABLES output has the following columns:
Database - the name of the database currently being used.
Table - name of the non temporary table(s) currently opened.
In_use - The number of table locks or lock requests there is in the database for the table.
Name_locked - Whether the table name is locked. Name locking is used for the operations like dropping or renaming tables.
If there are no privileges for a table, it does not show up in the output from SHOW OPEN TABLES.
Here is the another example of pattern matching
SHOW OPEN TABLES FROM employee LIKE 'e%';
Let execute the above and see the output :
Sample Output:
mysql> SHOW OPEN TABLES FROM employee LIKE 'e%'; +-----------+-----------+--------+-------------+ | Database | Table | In_use | Name_locked | +-----------+-----------+--------+-------------+ | employee | employees | 0 | 0 | +-----------+-----------+--------+-------------+ 1 row in set (0.00 sec)
MySQL: SHOW PLUGINS
The SHOW PLUGINS statement is used to display the information about server plugins. The information or Plugins is also available in the INFORMATION_SCHEMA.PLUGINS table.
Here is the syntax:
SHOW PLUGINS
See the following example.
SHOW PLUGINS\G
Let execute the above and see the output:
Sample Output:
mysql> SHOW PLUGINS\G *************************** 1. row *************************** Name: binlog Status: ACTIVE Type: STORAGE ENGINE Library: NULL License: GPL *************************** 2. row *************************** Name: MEMORY Status: ACTIVE Type: STORAGE ENGINE Library: NULL License: GPL *************************** 3. row *************************** Name: MyISAM Status: ACTIVE Type: STORAGE ENGINE Library: NULL License: GPL *************************** 4. row *************************** Name: MRG_MYISAM Status: ACTIVE Type: STORAGE ENGINE Library: NULL License: GPL ....
Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
MySQL : SHOW PRIVILEGES
The SHOW PRIVILEGES statement shows the list of system privileges that the MySQL server supports. The exact list of privileges depends on the version of the server which you are using.
Here is the syntax:
SHOW PRIVILEGES
See the following example.
SHOW PRIVILEGES\G
Let execute the above and see the output :
Sample Output:
mysql> SHOW PRIVILEGES\G *************************** 1. row *************************** Privilege: Alter Context: Tables Comment: To alter the table *************************** 2. row *************************** Privilege: Alter routine Context: Functions,Procedures Comment: To alter or drop stored functions/procedures *************************** 3. row *************************** Privilege: Create Context: Databases,Tables,Indexes Comment: To create new databases and tables *************************** 4. row *************************** Privilege: Create routine Context: Databases Comment: To use CREATE FUNCTION/PROCEDURE *************************** 5. row *************************** Privilege: Create temporary tables Context: Databases Comment: To use CREATE TEMPORARY TABLE *************************** 6. row *************************** Privilege: Create view Context: Tables Comment: To create new views ....
Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
MySQL : SHOW PROCEDURE CODE
The SHOW PROCEDURE CODE statement is used to display the internal implementation of the named stored procedure. This statement is a is available only for servers that have been built with debugging support.
Here is the syntax:
SHOW PROCEDURE CODE proc_name
See the following example, here the server has not built with debugging support.
mysql> SHOW PROCEDURE CODE job_data; ERROR 1289 (HY000): The 'SHOW PROCEDURE|FUNCTION CODE' feature is disabled; you need MySQL built with '--with-debug' to have it working
MySQL : SHOW PROCEDURE STATUS
This SHOW PROCEDURE STATUS statement returns the characteristics of a stored procedure, such as the database, name, type, creator, creation and modification dates, and character set information.
Here is the syntax:
SHOW PROCEDURE STATUS
[LIKE 'pattern' | WHERE expr]
See the following example.
SHOW PROCEDURE STATUS\G
Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output:
Sample Output:
mysql> SHOW PROCEDURE STATUS\G
*************************** 1. row ***************************
Db: empinfo
Name: myprocedure
Type: PROCEDURE
Definer: root@localhost
Modified: 2013-07-30 16:17:14
Created: 2013-07-30 16:17:14
Security_type: DEFINER
Comment:
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
Db: empinfo
Name: proc1
Type: PROCEDURE
Definer: root@localhost
Modified: 2013-07-31 10:44:07
Created: 2013-07-31 10:44:07
Security_type: DEFINER
Comment:
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
*************************** 3. row ***************************
Db: empinfo
Name: test
Type: PROCEDURE
Definer: root@localhost
Modified: 2013-06-20 17:28:09
Created: 2013-06-20 17:28:09
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
*************************** 4. row ***************************
Db: empinfo
Name: test1
Type: PROCEDURE
Definer: root@localhost
Modified: 2013-07-30 17:48:16
Created: 2013-07-30 17:48:16
Security_type: DEFINER
Comment:
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
4 rows in set (0.22 sec)
Let execute the example below using pattern matching and see the output :
Sample Output:
mysql> SHOW PROCEDURE STATUS like 'tes%'\G
*************************** 1. row ***************************
Db: empinfo
Name: test
Type: PROCEDURE
Definer: root@localhost
Modified: 2013-06-20 17:28:09
Created: 2013-06-20 17:28:09
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
Db: empinfo
Name: test1
Type: PROCEDURE
Definer: root@localhost
Modified: 2013-07-30 17:48:16
Created: 2013-07-30 17:48:16
Security_type: DEFINER
Comment:
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
2 rows in set (0.00 sec)
Let execute the example below using where and see the output:
Sample Output:
mysql> SHOW PROCEDURE STATUS WHERE Db = 'empinfo'\G
*************************** 1. row ***************************
Db: empinfo
Name: myprocedure
Type: PROCEDURE
Definer: root@localhost
Modified: 2013-07-30 16:17:14
Created: 2013-07-30 16:17:14
Security_type: DEFINER
Comment:
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
Db: empinfo
Name: proc1
Type: PROCEDURE
Definer: root@localhost
Modified: 2013-07-31 10:44:07
Created: 2013-07-31 10:44:07
Security_type: DEFINER
Comment:
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
*************************** 3. row ***************************
Db: empinfo
Name: test
Type: PROCEDURE
Definer: root@localhost
Modified: 2013-06-20 17:28:09
Created: 2013-06-20 17:28:09
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
*************************** 4. row ***************************
Db: empinfo
Name: test1
Type: PROCEDURE
Definer: root@localhost
Modified: 2013-07-30 17:48:16
Created: 2013-07-30 17:48:16
Security_type: DEFINER
Comment:
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
4 rows in set (0.02 sec)
MySQL : SHOW PROCESSLIST
The SHOW PROCESSLIST statement shows you which threads are running. If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field.
Here is the syntax:
SHOW [FULL] PROCESSLIST
See the following example.
SHOW PROCESSLIST\G
Let execute the above and see the output:
Sample Output:
mysql> SHOW FULL PROCESSLIST\G
*************************** 1. row ***************************
Id: 1
User: root
Host: localhost:1300
db: NULL
Command: Query
Time: 0
State: NULL
Info: SHOW FULL PROCESSLIST
1 row in set (0.00 sec)
Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout.
MySQL : SHOW RELAYLOG EVENTS
The SHOW RELAYLOG statement shows the events in the relay log of a replication slave. If you do not specify 'log_name', the first relay log is displayed.
Here is the syntax:
SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
If 'log_name', is not specified the first relay log is displayed. This statement has no effect on the master.
MySQL: SHOW SLAVE HOSTS
SHOW SLAVE HOSTS statement is used to display a list of replication slaves currently registered with the master.
Here is the syntax:
SHOW SLAVE HOSTS
MySQL: SHOW SLAVE STATUS
The SHOW SLAVE STATUS statement provides status information on essential parameters of the slave threads.
Here is the syntax:
SHOW SLAVE STATUS
The statement requires either the SUPER or REPLICATION CLIENT privilege.
MySQL : SHOW STATUS
The SHOW STATUS statement provides the information of server status. The LIKE clause along with this statement helps to match the specific variable. The usage of WHERE clause can fetch rows against general conditions. This statement does not require any privilege.
Here is the syntax :
SHOW [GLOBAL | SESSION] STATUS
[LIKE 'pattern' | WHERE expr]
See the following example.
SHOW STATUS;
Let execute the above and see the output:
Sample Output:
mysql> SHOW STATUS; +-----------------------------------+----------+ | Variable_name | Value | +-----------------------------------+----------+ | Aborted_clients | 0 | | Aborted_connects | 0 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 263 | | Bytes_sent | 2006 | | Com_admin_commands | 0 | ... | Compression | OFF | | Connections | 15 | | Created_tmp_disk_tables | 0 | | Created_tmp_files | 5 | | Created_tmp_tables | 0 | | Delayed_errors | 0 | ... | Flush_commands | 1 | | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | ... | Innodb_buffer_pool_pages_data | 19 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 0 | | Innodb_buffer_pool_pages_free | 493 | ... | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 14347 | ... | Tc_log_max_pages_used | 0 | | Tc_log_page_size | 0 | | Tc_log_page_waits | 0 | | Threads_cached | 0 | | Threads_connected | 1 | | Threads_created | 14 | | Threads_running | 1 | | Uptime | 2029 | | Uptime_since_flush_status | 2029 | +-----------------------------------+----------+ 291 rows in set (0.20 sec)
Here is the another example.
SHOW STATUS LIKE 'Qca%';
Let execute the above and see the output:
Sample Output:
mysql> SHOW STATUS LIKE 'Qca%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_free_blocks | 0 | | Qcache_free_memory | 0 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 0 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 0 | +-------------------------+-------+ 8 rows in set (0.00 sec)
MySQL : SHOW TABLE STATUS
The SHOW TABLE STATUS statement provides a lot of information about each non-TEMPORARY table. The LIKE clause, if present, indicates which table names to match. The usage of WHERE clause can fetch rows against general conditions.
Here is the syntax:
SHOW TABLE STATUS [{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
See the following example.
SHOW TABLE STATUS;
Let execute the above and see the output:
Sample Output:
Sample Output:
mysql> SHOW TABLE STATUS; +-------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ | countries | MyISAM | 10 | Dynamic | 25 | 23 | 580 | 281474976710655 | 3072 | 0 | NULL | 2013-03-09 17:52:17 | 2013-03-13 18:39:26 | 2013-03-13 18:39:22 | latin1_swedish_ci | NULL | | | | departments | MyISAM | 10 | Dynamic | 27 | 24 | 656 | 281474976710655 | 4096 | 0 | NULL | 2013-03-09 18:12:13 | 2013-03-13 17:54:15 | 2013-03-13 17:50:03 | latin1_swedish_ci | NULL | | | | employees | MyISAM | 10 | Dynamic | 107 | 66 | 7164 | 281474976710655 | 14336 | 0 | NULL | 2013-03-09 18:05:26 | 2013-03-13 18:36:09 | 2013-03-13 18:36:07 | latin1_swedish_ci | NULL | | | | job_history | MyISAM | 10 | Dynamic | 11 | 25 | 276 | 281474976710655 | 5120 | 0 | NULL | 2013-03-09 18:16:48 | 2013-03-13 18:31:20 | 2013-03-13 18:31:16 | latin1_swedish_ci | NULL | | | | jobs | MyISAM | 10 | Dynamic | 19 | 37 | 720 | 281474976710655 | 2048 | 0 | NULL | 2013-03-09 18:13:33 | 2013-03-13 17:50:41 | NULL | latin1_swedish_ci | NULL | | | | locations | MyISAM | 10 | Dynamic | 23 | 53 | 1220 | 281474976710655 | 5120 | 0 | NULL | 2013-03-09 17:58:56 | 2013-03-13 17:54:15 | 2013-03-13 17:46:40 | latin1_swedish_ci | NULL | | | ....
Here is another example of SHOW TABLE STATUS using pattern matching
SHOW TABLE STATUS FROM employee LIKE 'job%';
Let execute the above and see the output:
Sample Output:
mysql> SHOW TABLE STATUS FROM employee LIKE 'job%'; +-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ | job_history | MyISAM | 10 | Dynamic | 11 | 25 | 276 | 281474976710655 | 5120 | 0 | NULL | 2013-03-09 18:16:48 | 2013-03-13 18:31:20 | 2013-03-13 18:31:16 | latin1_swedish_ci | NULL | | | | jobs | MyISAM | 10 | Dynamic | 19 | 37 | 720 | 281474976710655 | 2048 | 0 | NULL | 2013-03-09 18:13:33 | 2013-03-13 17:50:41 | NULL | latin1_swedish_ci | NULL | | | +-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ 2 rows in set (0.00 sec)
Here is another example of SHOW TABLE STATUS using WHERE clause
SHOW TABLE STATUS FROM employee WHERE name='countries';
Let execute the above and see the output:
Sample Output:
mysql> SHOW TABLE STATUS FROM employee WHERE name='countries'; +-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ | countries | MyISAM | 10 | Dynamic | 25 | 23 | 580 | 281474976710655 | 3072 | 0 | NULL | 2013-03-09 17:52:17 | 2013-03-13 18:39:26 | 2013-03-13 18:39:22 | latin1_swedish_ci | NULL | | | +-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+ 1 row in set (0.00 sec)
MySQL : SHOW TABLES
SHOW TABLES lists the non-TEMPORARY tables in a given database. The LIKE clause, if present, indicates which table names to match. The usage of WHERE clause can fetch rows against general conditions.
Here is the syntax :
SHOW [FULL] TABLES [{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
See the following example.
SHOW TABLES;
Let execute the above and see the output :
Sample Output:
mysql> SHOW TABLES; +---------------------+ | Tables_in_empinfo | +---------------------+ | countries | | departments | | employees | | job_history | | jobs | | locations | | myview | | regions | | table1 | | table11 | | table111 | | table112 | | table113 | | table114 | | table12 | | table13 | | table2 | | test1 | | test2 | +---------------------+ 19 rows in set (0.39 sec)
Here is alternate statements for the above output
SHOW TABLES FROM empinfo;
SHOW TABLES IN empinfo;
Here is the another example of SHOW TABLES with pattern matching
SHOW TABLES FROM empinfo LIKE 'e%';
Let execute the above and see the output:
Sample Output:
mysql> SHOW TABLES FROM empinfo LIKE 'e%'; +--------------------------+ | Tables_in_empinfo (e%) | +--------------------------+ | employees | +--------------------------+ 1 row in set (0.02 sec)
Here is the another example of SHOW TABLES with WHERE clause
SHOW TABLES FROM empinfo
WHERE Tables_in_empinfo='employees';
Let execute the above and see the output:
Sample Output:
mysql> SHOW TABLES FROM empinfo WHERE Tables_in_empinfo='employees'; +---------------------+ | Tables_in_empinfo | +---------------------+ | employees | +---------------------+ 1 row in set (0.00 sec)
MySQL : SHOW TRIGGERS
SHOW TRIGGERS statement is used to list the triggers currently defined for tables in a database
Here is the syntax:
SHOW TRIGGERS [{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
MySQL : SHOW VARIABLES
The SHOW VARIABLES statement shows the values of MySQL system variables. The LIKE clause, if present, indicates which table names to match. The usage of WHERE clause can fetch rows against general conditions. This statement does not require any privilege. It requires only the ability to connect to the server.
Here is the syntax:
SHOW [GLOBAL | SESSION] VARIABLES
[LIKE 'pattern' | WHERE expr]
See the following example.
SHOW VARIABLES;
Let execute the above and see the output :
Sample Output:
mysql> SHOW VARIABLES; +-----------------------------------------+---------------------------------------------+ | Variable_name | Value | +-----------------------------------------+---------------------------------------------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | | back_log | 50 | | basedir | c:\wamp\bin\mysql\mysql5.1.36\ | | big_tables | OFF | | binlog_cache_size | 32768 | ...
Here is another example of SHOW VARIABLES with GLOBAL
SHOW GLOBAL VARIABLES;
Let execute the above and see the output:
Sample Output:
mysql> SHOW GLOBAL VARIABLES; +-----------------------------------------+----------------------------------------------+ | Variable_name | Value | +-----------------------------------------+----------------------------------------------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | | back_log | 50 | | basedir | c:\wamp\bin\mysql\mysql5.1.36\ | | big_tables | OFF | ...
Here is another example of SHOW VARIABLES with LIKE
SHOW VARIABLES LIKE 'time%';
Let execute the above and see the output:
Sample Output:
mysql> SHOW VARIABLES LIKE 'time%'; +---------------+------------+ | Variable_name | Value | +---------------+------------+ | time_format | %H:%i:%s | | time_zone | SYSTEM | | timed_mutexes | OFF | | timestamp | 1375447849 | +---------------+------------+ 4 rows in set (0.00 sec)
Here is another example of SHOW VARIABLES with LIKE
SHOW VARIABLES LIKE 'time%';
MySQL: SHOW WARNINGS
The SHOW WARNINGS statement is used to display the warnings,errors, and notes that resulted from the last statement in the current session that generated messages. It shows nothing if the last statement does not generate any message.
Here is the syntax :
SHOW WARNINGS [LIMIT [offset,] row_count] SHOW COUNT(*) WARNINGS
The LIMIT clause can be used to specify the number of rows to be retrieved.
The offset is an argument, which LIMIT takes optionally to retrieve the number of rows. When mention two arguments, the first one is from a particular position and the second one is a number of rows after the first one position.
The offset of the initial row is 0 (not 1)
The SHOW COUNT(*) WARNINGS statement is used to displays the number of warnings.
See the following example:
Here, in the below statement, there is an error. Execute this statement an error message will be generated.
SELECT * FORM employees;
and now, here is the statement-
SHOW WARNINGS\G
Here '\G' statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output:
Sample Output:
mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Error Code: 1064 Message: 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 'FORM EMPLOYEES' at line 1 1 row in set (0.00 sec)
SHOW COUNT(*) WARNINGS;
Let execute the above and see the output:
Sample Output:
mysql> SHOW COUNT(*) WARNINGS; +-------------------------+ | @@session.warning_count | +-------------------------+ | 1 | +-------------------------+ 1 row in set (0.13 sec)
A number of warnings can also be retrieve using the warning_count variable in a SELECT statement.
SELECT @@warning_count;
Let execute the above and see the output:
Sample Output:
mysql> SELECT @@warning_count; +-----------------+ | @@warning_count | +-----------------+ | 1 | +-----------------+ 1 row in set (0.03 sec)
Reference: MySQL 5.6 Manual
Previous:
MySQL Security
Next:
MySQL DROP
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/mysql/mysql-show.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics