MySQL常用SQL语句
👉 本文共约312个字，系统预计阅读时间或需2分钟。
MySQL表结构
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | -- MySQL表结构 SELECT a.TABLE_NAME "表", a.COLUMN_NAME "列", a.COLUMN_TYPE "类型", a.COLUMN_DEFAULT "默认值", a.IS_NULLABLE "是否为空", a.CHARACTER_SET_NAME "表字符集", a.COLLATION_NAME "校验字符集", CONCAT(a.COLUMN_COMMENT,a.COLUMN_KEY, a.EXTRA) "列备注", b.TABLE_COMMENT "表备注" , b.ENGINE "引擎" FROM information_schema.COLUMNS a,information_schema.TABLES b WHERE a.TABLE_SCHEMA=b.TABLE_SCHEMA AND a.TABLE_SCHEMA='lhrdb' AND a.TABLE_NAME=b.TABLE_NAME; |
MySQL索引大小
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | select iis.database_name, iis.table_name, iis.index_name, round((iis.stat_value@@innodb_page_size)/1024/1024, 2) SizeMB, -- round(((100/(SELECT INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = iis.table_name and t.TABLE_SCHEMA = iis.database_name))(stat_value*@@innodb_page_size)), 2) Percentage, s.NON_UNIQUE, s.INDEX_TYPE, GROUP_CONCAT(s.COLUMN_NAME order by SEQ_IN_INDEX) COLUMN_NAME from (select * from mysql.innodb_index_stats WHERE index_name not in ('PRIMARY','GEN_CLUST_INDEX') and stat_name='size' order by (stat_value@@innodb_page_size) desc limit 10 ) iis left join INFORMATION_SCHEMA.STATISTICS s on (iis.database_name=s.TABLE_SCHEMA and iis.table_name=s.TABLE_NAME and iis.index_name=s.INDEX_NAME) GROUP BY iis.database_name,iis.TABLE_NAME,iis.INDEX_NAME,(iis.stat_value@@innodb_page_size),s.NON_UNIQUE,s.INDEX_TYPE order by (stat_value*@@innodb_page_size) desc; |
MySQL索引信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | -- MySQL索引信息 SELECT TABLE_SCHEMA, TABLE_NAME, NON_UNIQUE, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, INDEX_TYPE, CONCAT(COMMENT,INDEX_COMMENT) INDEX_COMMENT FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'lhrdb' ORDER BY TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,SEQ_IN_INDEX; -- MySQL索引信息 方便生成alter语句 SELECT a.TABLE_SCHEMA, a.TABLE_NAME, a.INDEX_NAME, MAX(a.NON_UNIQUE) NON_UNIQUE, MAX(a.INDEX_TYPE) INDEX_TYPE, MAX(a.INDEX_COMMENT) INDEX_COMMENT, GROUP_CONCAT(a.COLUMN_NAME order by SEQ_IN_INDEX) COLUMN_NAME FROM (SELECT TABLE_SCHEMA, TABLE_NAME, NON_UNIQUE, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, INDEX_TYPE, CONCAT(COMMENT,INDEX_COMMENT) INDEX_COMMENT FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'lhrdb' ORDER BY TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,SEQ_IN_INDEX) a GROUP BY a.TABLE_SCHEMA,a.TABLE_NAME,a.INDEX_NAME; |
MySQL无主键的表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | --- MySQL无主键的表 select table_schema, table_name from information_schema.tables where table_type='BASE TABLE' and (table_schema, table_name) not in ( select /*+ subquery(materialization) */ a.TABLE_SCHEMA,a.TABLE_NAME from information_schema.TABLE_CONSTRAINTS a where a.CONSTRAINT_TYPE in ('PRIMARY KEY','UNIQUE') and table_schema not in ('mysql', 'information_schema', 'sys', 'performance_schema') ) AND table_schema not in ('mysql', 'information_schema', 'sys', 'performance_schema') limit 100 ; select table_schema, table_name from information_schema.tables where table_type='BASE TABLE' and (table_schema,table_name) not in (select /*+ subquery(materialization) */ table_schema, table_name from information_schema.columns where column_key in ( 'PRI','UNI') ) AND table_schema not in ('mysql', 'information_schema', 'sys', 'performance_schema') and table_schema not in ('mysql', 'information_schema', 'sys', 'performance_schema') limit 100 ; |
MySQL对象统计
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | ---- MySQL迁移完成后的数据对象 select db 数据库,type 对象类型,cnt 对象数量 from (select '表' type,table_schema db, count(*) cnt from information_schema.TABLES a where table_type='BASE TABLE' group by table_schema union all select '事件' type,event_schema db,count(*) cnt from information_schema.EVENTS b group by event_schema union all select '触发器' type,trigger_schema db,count(*) cnt from information_schema.TRIGGERS c group by trigger_schema union all select '存储过程' type,routine_schema db,count(*) cnt from information_schema.ROUTINES d whereROUTINE_TYPE = 'PROCEDURE' group by db union all select '函数' type,routine_schema db,count(*) cnt from information_schema.ROUTINES d whereROUTINE_TYPE = 'FUNCTION' group by db union all select '视图' type,table_schema db,count(*) cnt from information_schema.VIEWS f group by table_schema ) t where db='lhrdb' order by db,type; drop view vw_ob_lhr; create view vw_ob_lhr as select db ,type ,cnt from (select 'TABLE' type,table_schema db, count(*) cnt from information_schema.TABLES a where table_type='BASE TABLE' group by table_schema union all select 'EVENTS' type,event_schema db,count(*) cnt from information_schema.EVENTS b group by event_schema union all select 'TRIGGERS' type,trigger_schema db,count(*) cnt from information_schema.TRIGGERS c group by trigger_schema union all select 'PROCEDURE' type,routine_schema db,count(*) cnt from information_schema.ROUTINES d where ROUTINE_TYPE = 'PROCEDURE' group by db union all select 'FUNCTION' type,routine_schema db,count(*) cnt from information_schema.ROUTINES d where ROUTINE_TYPE = 'FUNCTION' group by db union all select 'VIEWS' type,table_schema db,count(*) cnt from information_schema.VIEWS f group by table_schema ) t order by db,type; select * from vw_ob_lhr where db='lhrdb'; |
mysql建库
1 2 3 4 | create database lhrdb1 charset utf8mb4; create database lhrdb2 character set utf8mb4; |
mysql快速创建大量表
1 2 3 4 5 6 7 8 | #!/bin/bash for i in {1..1000} do mysql -uroot -plhr -h192.168.66.35 -P3317 lhrdb -e "create table with_pk${i} (pk int primary key)" mysql -uroot -plhr -h192.168.66.35 -P3317 lhrdb -e "create table without_pk${i} (col int)" done |
linux每秒插入MySQL
1 2 3 4 5 6 7 8 9 | CREATE TABLE lhdb.mytest ( id int(11) NOT NULL AUTO_INCREMENT, ctime datetime DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; while true; do mysql -S /tmp/mysql.sock -e 'insert into lhdb.mytest(ctime)values(now())';date;sleep 1;done |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
修改字符集
1 2 | alter database lhrdb charset utf8mb4; |
启动和关闭MySQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | -- 启动MySQL mysqld --defaults-file=/etc/my.cnf --user=mysql & mysqld_safe --defaults-file=/etc/my.cnf --user=mysql & service mysql start # systemctl start mysqld /etc/init.d/mysql start mysqld_multi start #多实例 net start mysql #Windows -- 关闭MySQL mysqladmin -uroot -plhr -S/tmp/mysql3306.sock shutdown #版本对应 service mysql stop # systemctl stop mysqld /etc/init.d/mysql stop mysqld_multi stop #多实例 net stop mysql #Windows mysql> shutdown; # above MySQL 5.7.9 -- 杀死MySQL killall mysqld killall -9 mysqld pkill -9 mysqld |
测试负载均衡
1 | for i in $(seq 1 10); do mysql -uroot -plhr -h192.168.59.130 -P33066 -e 'select @@server_id;'; done | egrep '[0-9]' |
