mysql5 操作命令（一）_mysql

 

mysql5 操作命令（1）

 

/* 查看表大小 */

SELECT CONCAT( TRUNCATE( SUM( data_length ) /1024 /1024, 2 ) ,  'MB' ) AS data_size, CONCAT( TRUNCATE( SUM( max_data_length ) /1024 /1024, 2 ) ,  'MB' )AS max_data_size, CONCAT( TRUNCATE( SUM( data_free ) /1024 /1024, 2 ) ,  'MB' )AS data_free, CONCAT( TRUNCATE( SUM( index_length ) /1024 /1024, 2 ) ,  'MB' ) AS index_size FROM information_schema.tables WHERE TABLE_NAME =  'db_name.table_name' limit 0, 30;

 

/*查看数据库大小*/

SELECT (sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024*1024 AS SIZE FROM information_schema.TABLES where TABLE_SCHEMA='db_name';

 得到的结果是以字节为单位，除1024为K，除1048576(=1024*1024)为M。

 

 

查看mysql参数信息

select @@参数名

mysql> select @@innodb_lock_wait_timeout; +----------------------------+ | @@innodb_lock_wait_timeout | +----------------------------+ |                         20 | +----------------------------+

 

临时设置参数名，退出后即失效

set @@参数名

set @@innodb_lock_wait_timeout=20;

 

 

1.获取mysql所有表

SELECT * FROM information_schema.TABLES; (WHERE TABLE_SCHEMA=’数据库名称’;) 

SELECT * FROM information_schema.TABLES WHERE TABLE_NAME=’t001_user_remain’;

 

2.获取表字段

SELECT * FROM information_schema.COLUMNS; (WHERE TABLE_NAME=’表名’；) 

SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME=’t001_user_remain’;

 

3.获取表键值

SELECT * FROM information_schema.KEY_COLUMN_USAGE; (WHERE TABLE_NAME=’表名’) 

SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME=’t001_user_remain’;

 

4.获取表check约束

SELECT * FROM information_schema.TABLE_CONSTRAINTS; (WHERE TABLE_NAME=’表名’;)

SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME=’t001_user_remain’;

 

5.获取表索引

SELECT * FROM information_schema.STATISTICS; (WHERE TABLE_NAME=’表名’) 

SELECT * FROM information_schema.STATISTICS where TABLE_NAME=’t001_user_remain’

 

提供了关于存储子程序（存储程序和函数）的信息。

SELECT * FROM information_schema.ROUTINES; 

 

给出了关于数据库中的视图的信息

SELECT * FROM information_schema.VIEWS;

 

提供了关于触发程序的信息

SELECT * FROM information_schema.TRIGGERS; 

 

 

描述了存在约束的表。以及表的约束类型。

SELECT * FROM information_schema.TABLE_CONSTRAINTS where TABLE_NAME=’t001_user_remain’;

 

描述了具有约束的键列

SELECT * FROM information_schema.KEY_COLUMN_USAGE where TABLE_NAME=’t001_user_remain’; 

 

 

(字符集）表：提供了mysql实例可用字符集的信息

SELECT * FROM information_schema.CHARACTER_SETS; 

 

提供了关于各字符集的对照信息

SELECT * FROM information_schema.COLLATIONS; 

 

指明了可用于校对的字符集。这些列等效于COLLATION的前两个显示字段

SELECT * FROM information_schema.COLLATION_CHARACTER_SET_APPLICABILITY; 

 

用户权限表：给出了关于用户权限的信息。该信息源自mysql.user授权表。是非标准表。

SELECT * FROM information_schema.USER_PRIVILEGES;

 

方案权限表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表。

SELECT * FROM information_schema.SCHEMA_PRIVILEGES;

 

（表权限）表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。

SELECT * FROM information_schema.TABLE_PRIVILEGES; 

 

（列权限）表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。

SELECT * FROM information_schema.COLUMN_PRIVILEGES; 

 

 

 

 

显示mysql中所有数据库信息。

SELECT * FROM information_schema.SCHEMATA;

 

显示mysql中所有数据库中的表的信息（包括视图）。详细表述了某个表属于哪个数据库，表类型，表引擎，创建时间等详细信息。

SELECT * FROM information_schema.TABLES where TABLE_SCHEMA=’mysql’;

 

 

显示mysql中所有表中的列信息。详细表述了某张表的所有列以及每个列的详细信息。（类型，长度，字符集等）

SELECT * FROM information_schema.COLUMNS where TABLE_NAME=’t001_user_remain’; 

 

显示了所有表索引的详细信息（主键，外键，所属数据库，列名等详细信息）

SELECT * FROM information_schema.STATISTICS where TABLE_NAME=’t001_user_remain’; 

 

/*查看运行比较久的事务*/ 

SELECT * FROM information_schema.INNODB_TRX/G

mysql> SELECT * FROM information_schema.INNODB_TRX/G *************************** 1. row ***************************                     trx_id: 1743662                  trx_state: LOCK WAIT                trx_started: 2014-08-16 16:44:09      trx_requested_lock_id: 1743662:209:3:2           trx_wait_started: 2014-08-16 16:44:09                 trx_weight: 2        trx_mysql_thread_id: 349                  trx_query: update t018_project set pr_code='mmm' where pr_id=2        trx_operation_state: starting index read          trx_tables_in_use: 1          trx_tables_locked: 1           trx_lock_structs: 2      trx_lock_memory_bytes: 376            trx_rows_locked: 1          trx_rows_modified: 0    trx_concurrency_tickets: 0        trx_isolation_level: REPEATABLE READ          trx_unique_checks: 1     trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL  trx_adaptive_hash_latched: 0  trx_adaptive_hash_timeout: 10000           trx_is_read_only: 0 trx_autocommit_non_locking: 0

 

 

 查看锁信息，显示当前被锁的信息 /*查看正在锁的事务*/

 select * from information_schema.innodb_locks

mysql> select * from information_schema.innodb_locks; +-----------------+-------------+-----------+-----------+---------------------------+------------+------------+-----------+----------+-----------+ | lock_id         | lock_trx_id | lock_mode | lock_type | lock_table                | lock_index | lock_space | lock_page | lock_rec | lock_data | +-----------------+-------------+-----------+-----------+---------------------------+------------+------------+-----------+----------+-----------+ | 1743661:209:3:2 | 1743661     | X         | RECORD    | `nowagame`.`t018_project` | PRIMARY    |        209 |         3 |        2 | 2         | | 1743660:209:3:2 | 1743660     | X         | RECORD    | `nowagame`.`t018_project` | PRIMARY    |        209 |         3 |        2 | 2         | +-----------------+-------------+-----------+-----------+---------------------------+------------+------------+-----------+----------+-----------+ 2 rows in set (0.01 sec)

 

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;