mysql5 操作命令（1）

原创

stephen510 2023-06-14 06:39:14 博主文章分类：mysql ©著作权

文章标签 数据库 mysql 授权表 文章分类 HarmonyOS 后端开发

©著作权归作者所有：来自51CTO博客作者stephen510的原创作品，请联系作者获取转载授权，否则将追究法律责任

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。

/* 调优必备返回语句执行的过程信息 */

explain [extended] select ... from ... where ...

/* 分析表*/

ANALYZE TABLE tb_name;

/*查看表的索引使用情况*/

SHOW INDEX FROM tb_name ;

查看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;