MYSQL常用高频查询

1. msyql 常用查询

目录

• 1. msyql 常用查询
  • 1.1. 查询重复记录
  • 1.2. 查询库中所有表的大小，按照大小排序
  • 1.3. 查询库中指定表的大小
  • 1.4. 查看所有数据库的大小
  • 1.5. 查看指定数据库容量大小，包含记录数
  • 1.6. 查看前30个大表信息
  • 1.7. 查看分区表信息
  • 1.8. 查看表的数据估算值和表的估算大小
  • 1.9. 查看主键信息
  • 1.10. 查看没有主键的表
  • 1.11. 查看没有索引的表
  • 1.12. 查询前n条记录
  • 1.13. 查询后n条记录
  • 1.14. 查询从指定位置开始的n条记录
  • 1.15. 查询数据库中的冗余索引和重复索引
    • 1.15.1. 冗余索引清理规则
    • 1.15.2. 查询冗余索引
  • 1.16. mysql查询外键关系
  • 1.17. 查询会话信息
  • 1.18. 查询当前连接的进程使用的内存信息
• 2. 常用命令
  • 2.1. mysql帮助命令的使用
  • 2.2. mysql比较常用的视图（注意一般需要大写）
  • 2.3. Mysql中查询系统时间的方法
  • 2.4. Mysql select INTO OUTFILE 配置
    • 2.4.1. secure_file_priv
    • 2.4.2. secure_file_priv配置
    • 2.4.3. 授予普通用户select outfile 权限
  • 2.5. mysql隔离级别
    • 2.5.1. 查看当前会话隔离级别
    • 2.5.2. 设置当前会话隔离级别
    • 2.5.3. 查看系统当前隔离级别
    • 2.5.4. 设置系统当前隔离级别
  • 2.6. 查看某个用户执行过的sql语句
  • 2.7. 会话和连接相关
    • 2.7.1. 查看当前的连接数量和并发数
    • 2.7.2. 查找TOP sql -性能视图方式
      • 2.7.2.1. 查找TOP sql
      • 2.7.2.2. 查询平均时间最长的sql
    • 2.7.3. 查找TOP sql -存储过程方式
      • 2.7.3.1. diagnostics()
      • 2.7.3.2. ps_trace_statement_digest()
      • 2.7.3.3. statement_performance_analyzer()
      • 2.7.3.4. ps_trace_thread()
  • 2.8. 查看参数文件位置：
  • 2.9. msyql innodb缓存池的命中率
  • 2.10. mysql拼接批量alert表语句
  • 2.11. mysql 替换字段中的换行符
  • 2.12. 查询mysql自增主键使用情况
  • 2.13. mysql新增列，并将该列放到指定列之后
  • 2.14. mysql临时表解决create table x select 字段1，字段2 from tab
  • 2.15. mysql 手动锁表操作：
  • 2.16. 添加函数索引
  • 线上无锁添加索引：加索引的语句不加锁
  • 查询视图的定义者
  • 5.7修改函数定义者
  • mysql 8.0统一修改函数定义者
  • 查看mysql中库下面的所有索引和字段信息

1.1. 查询重复记录

select * from TD_PC_TRADE where TRADE_ID in (select TRADE_ID from TD_PC_TRADE group by TRADE_ID having count(TRADE_ID) > 1);

1.2. 查询库中所有表的大小，按照大小排序

SELECT TABLE_SCHEMA ,
       TABLE_NAME ,
       round((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 2) AS TOTAL_GB ,
       round(DATA_LENGTH / 1024 / 1024 / 1024, 2) AS DATA_GB ,
       round(INDEX_LENGTH / 1024 / 1024 / 1024, 2) AS INDEX_GB ,
       round(DATA_FREE / 1024 / 1024 / 1024, 2) free_GB ,
       TABLE_ROWS
FROM information_schema.tables t
WHERE TABLE_TYPE NOT IN ( 'SYSTEM VIEW' ,
                          'VIEW' )
  AND tABLE_SCHEMA NOT IN ('information_schema',
                          'mysql',
                          'performance_schema',
                          'sys')
ORDER BY TOTAL_GB DESC;

1.3. 查询库中指定表的大小

SELECT TABLE_SCHEMA ,
       TABLE_NAME ,
       round((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 2) AS TOTAL_GB ,
       round(DATA_LENGTH / 1024 / 1024 / 1024, 2) AS DATA_GB ,
       round(INDEX_LENGTH / 1024 / 1024 / 1024, 2) AS INDEX_GB ,
       round(DATA_FREE / 1024 / 1024 / 1024, 2) free_GB ,
       TABLE_ROWS
FROM information_schema.tables t
WHERE TABLE_TYPE NOT IN ( 'SYSTEM VIEW' ,
                          'VIEW' )
  AND tABLE_SCHEMA NOT IN ('information_schema',
                          'mysql',
                          'performance_schema',
                          'sys')
	AND TABLE_NAME in('tb_tags',
                          '20171025artist',
                          'huawei_tags');	

1.4. 查看所有数据库的大小

SELECT
  TABLE_SCHEMA AS "库名",
  TRUNCATE(SUM(`DATA_LENGTH`) / 1024 / 1024/1024, 2) AS "表所占空间（GB）",
  TRUNCATE(SUM(`INDEX_LENGTH`) / 1024 / 1024/1024, 2) AS "索引所占空间（GB）",
  TRUNCATE((SUM(`DATA_LENGTH`) + SUM(`INDEX_LENGTH`)) / 1024 / 1024/1024,2) AS "空间累计（GB）"
FROM
  information_schema.`TABLES`
  where TABLE_SCHEMA not in('information_schema',
                          'mysql',
                          'performance_schema',
                          'sys')
GROUP BY `TABLE_SCHEMA`;

1.5. 查看指定数据库容量大小，包含记录数

select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql';

1.6. 查看前30个大表信息

SELECT TABLE_SCHEMA ,
       TABLE_NAME ,
       round((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 2) AS TOTAL_GB ,
       round(DATA_LENGTH / 1024 / 1024 / 1024, 2) AS DATA_GB ,
       round(INDEX_LENGTH / 1024 / 1024 / 1024, 2) AS INDEX_GB ,
       round(DATA_FREE / 1024 / 1024 / 1024, 2) free_GB ,
       TABLE_ROWS
FROM information_schema.tables t
WHERE TABLE_TYPE NOT IN ( 'SYSTEM VIEW' ,
                          'VIEW' )
  AND tABLE_SCHEMA NOT IN ('information_schema',
                          'mysql',
                          'performance_schema',
                          'sys')
ORDER BY TOTAL_GB DESC LIMIT 30;

1.7. 查看分区表信息

SELECT TABLE_SCHEMA,
       TABLE_NAME,
       PARTITION_NAME,
       PARTITION_METHOD,
       SUBPARTITION_NAME,
       SUBPARTITION_METHOD,
       PARTITION_EXPRESSION,
       PARTITION_DESCRIPTION ,
       CREATE_TIME,
       CHECK_TIME
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE table_schema NOT IN ('mysql',
                           'information_schema',
                           'sys',
                           'performance_schema')
  AND PARTITION_NAME is not NULL ;

1.8. 查看表的数据估算值和表的估算大小

show table status like 'test' \G;

1.9. 查看主键信息

SELECT TABLE_SCHEMA,
       TABLE_NAME,
       GROUP_CONCAT(COLUMN_NAME,' ',COLUMN_TYPE) AS col ,
       COLUMN_KEY
FROM information_schema.COLUMNS
WHERE COLUMN_KEY = 'PRI'
  AND table_schema NOT IN ('mysql',
                           'information_schema',
                           'sys',
                           'performance_schema')
GROUP BY TABLE_SCHEMA,
         TABLE_NAME,
         COLUMN_KEY;

1.10. 查看没有主键的表

SELECT DISTINCT t.table_schema,
                t.table_name
FROM information_schema.tables AS t
LEFT JOIN information_schema.columns AS c ON t.table_schema = c.table_schema
AND t.table_name = c.table_name
AND c.column_key = 'PRI'
WHERE t.table_schema NOT IN ('information_schema',
                             'mysql',
                             'performance_schema')
  AND c.table_name IS NULL
  AND t.table_type != 'VIEW';

1.11. 查看没有索引的表

SELECT T1.TABLE_SCHEMA,
       T1.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS T1
JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA
AND T1.TABLE_NAME=T2.TABLE_NAME
WHERE T1.TABLE_SCHEMA NOT IN ('SYS' ,
                              'MYSQL',
                              'INFORMATION_SCHEMA' ,
                              'PERFORMANCE_SCHEMA')
  AND T2. TABLE_TYPE='BASE TABLE'
GROUP BY T1.TABLE_SCHEMA,
         T1.TABLE_NAME HAVING MAX(COLUMN_KEY)='';

1.12. 查询前n条记录

SELECT * FROM tb_name LIMIT 0,$N;

1.13. 查询后n条记录

SELECT * FROM tb_stu ORDER BY id ASC LIMIT $n

1.14. 查询从指定位置开始的n条记录

SELECT * FROM tb_stu ORDER BY id ASC LIMIT a,N
a ：表示从第a条数据开始
N :表示取n条记录

1.15. 查询数据库中的冗余索引和重复索引

1.15.1. 冗余索引清理规则

• 第一个索引是第二个索引的左前缀索引, 删除第一个索引
• 有主键, 用不到唯一索引, 删除唯一索引
• 有主键, 用不到普通索引, 删除普通索引
• 有唯一索引, 用不到普通索引, 删除普通索引
• 有联合主键索引, 用不到普通组合索引, 删除普通组合索引
• 索引重复, 删除其中一个索引
• 普通索引默认以主键索引做后缀, 重建普通索引

1.15.2. 查询冗余索引

use information_schema;
SELECT
	a.TABLE_SCHEMA AS '数据名',
	a.TABLE_NAME AS '表名',
	a.INDEX_NAME AS '索引1',
	b.INDEX_NAME AS '索引2',
	a.COLUMN_NAME AS '重复列名'
FROM
	STATISTICS a
JOIN STATISTICS b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
AND a.TABLE_NAME = b.TABLE_NAME
AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX
AND a.COLUMN_NAME = b.COLUMN_NAME
WHERE
	a.SEQ_IN_INDEX = 1
AND a.INDEX_NAME <> b.INDEX_NAME;

SELECT a.TABLE_SCHEMA, a.TABLE_NAME, a.COLUMN_NAME, 
a.INDEX_NAME AS 'index1', b.INDEX_NAME AS 'index2'
FROM information_schema.STATISTICS a 
JOIN information_schema.STATISTICS b 
ON a.TABLE_SCHEMA = b.TABLE_SCHEMA    
AND a.TABLE_NAME = b.TABLE_NAME 
AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX   
AND a.COLUMN_NAME = b.COLUMN_NAME 
WHERE a.SEQ_IN_INDEX = 1 AND a.INDEX_NAME <> b.INDEX_NAME;

1.16. mysql查询外键关系

SELECT C.TABLE_SCHEMA ,
       C.REFERENCED_TABLE_NAME father_table,
       GROUP_CONCAT(c.REFERENCED_COLUMN_NAME
                    ORDER BY c.ORDINAL_POSITION separator ',') AS father_table_col,
       C.TABLE_NAME child_table,
       GROUP_CONCAT(c.COLUMN_NAME
                    ORDER BY c.ORDINAL_POSITION separator ',') AS child_table_col,
       C.CONSTRAINT_NAME ,
       R.UPDATE_RULE ,
       R.DELETE_RULE
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE C,
     INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
WHERE C.CONSTRAINT_SCHEMA=R.CONSTRAINT_SCHEMA
  AND R.TABLE_NAME = C.TABLE_NAME
  AND R.CONSTRAINT_NAME = C.CONSTRAINT_NAME
  AND R.REFERENCED_TABLE_NAME = C.REFERENCED_TABLE_NAME
  AND C.REFERENCED_TABLE_NAME IS NOT NULL
GROUP BY C.TABLE_SCHEMA,
         C.REFERENCED_TABLE_NAME ,
         C.TABLE_NAME,
         C.CONSTRAINT_NAME ,
         R.UPDATE_RULE ,
         R.DELETE_RULE;

1.17. 查询会话信息

select* from information_schema.processlist where COMMAND<>'Sleep' \G;

1.18. 查询当前连接的进程使用的内存信息

SELECT
       t.PROCESSLIST_ID,SUM(m.CURRENT_NUMBER_OF_BYTES_USED) AS TOTAL
FROM
        performance_schema.memory_summary_by_thread_by_event_name m
INNER JOIN
        performance_schema.threads t
ON
        m.THREAD_ID = t.THREAD_ID;

2. 常用命令

2.1. mysql帮助命令的使用

在mysql的使用过程中常常会遇到下面的问题：

• 某个操作语法忘记了，如何快速查找？
• 如何快速知道当前版本上某个字段类型的取值范围？
• 当前版本都支持哪些函数？希望有例子说明
• 当前版本是否支持某个功能

一、想知道能够提供哪些帮助
使用

mysql>? contents
2、查看支持的数据类型
mysql>? data types
3、查看int这个数据类型的特点，取值范围等
mysql>? int
4、查看show 命令的作用
mysql>? show

2.2. mysql比较常用的视图（注意一般需要大写）

schemata:该视图提供了当前mysql实例中的所有的数据库的信息，show databases的结果就是从该表中取得数据

tables：该表提供了关于数据库中的表的信息（包括视图），详细表述了某个表属于哪个schema、表的类型、表引擎、创建时间等信息
show tables from schemaname 的结果就是从该表中取出的数据

columns:该表提供了表中的列的信息，详细表述了某张表的所有列以及每个列的信息，
show clumns from schemaname.tablename的结果就是来自该表

statistics:该表提供了关于表的索引的信息，show index schemaname.tablename 取自该表

2.3. Mysql中查询系统时间的方法

第一种方法：select current_date；

mysql> select current_date as Systemtime;
+------------+
| Systemtime |
+------------+
| 2009-07-29 |
+------------+

第二种方法：select now()

mysql> select now() as Systemtime;
+---------------------+
| Systemtime          |
+---------------------+
| 2009-07-29 19:06:07 |
+---------------------+

第三种方法：select sysdate()

mysql> select sysdate() as Systemtime;
+---------------------+
| Systemtime          |
+---------------------+
| 2009-07-29 19:06:45 |

2.4. Mysql select INTO OUTFILE 配置

Mysql可使用 into outfile 参数把表中数据导出到文件，
例如可用以下命令把 test 表的数据导出到 test.txt

SELECT * FROM newdb.test INTO OUTFILE 'test.txt';

以上命令在mysql5.6下运行没有问题，但在mysql5.7下运行则出现了以下错误：'ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv
option so it cannot execute this statement'

2.4.1. secure_file_priv

查看官方文档，secure_file_priv参数用于限制LOAD DATA, SELECT …OUTFILE,
LOAD_FILE()传到哪个指定目录。secure_file_priv 为 'NULL' 时，表示限制mysqld不允许导入或导出。

• secure_file_priv 为 '/tmp' 时，表示限制mysqld只能在/tmp目录中执行导入导出;
• secure_file_priv '没有值'时，表示不限制mysqld在任意目录的导入导出。

show global variables like '%secure_file_priv%';

2.4.2. secure_file_priv配置

因为 secure_file_priv 参数是只读参数，'不能使用set global命令修改'。

mysql> # set global secure_file_priv='';
'ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable'

打开my.cnf ，加入以下语句后重启mysql。# vim /etc/my.cnf

[mysqld]
secure-file-priv = ""

2.4.3. 授予普通用户select outfile 权限

GRANT FILE ON *.* TO `circleadmin`@`localhost`;
注意： file 权限是全局， 不可以单独指定给某个数据库授权，只能是*.*

2.5. mysql隔离级别

数据库事务的隔离级别

1）级别1：read uncommitted : 
   读取尚未提交的数据，哪个问题都不能解决

2）级别2：read committed：
   读取已经提交的数据（可以读取到其他事务提交的update更新和insert新增），可以解决脏读，oracle默认的

3）级别4：repeatable read：
   重读读取（只能读取其他事务已经提交的insert新增数据），可以解决脏读和不可重复读，mysql默认的

4）级别8：serializable：
   串行化：可以解决 脏读 不可重复读 和 虚读---相当于锁表

2.5.1. 查看当前会话隔离级别

SELECT @@tx_isolation;

2.5.2. 设置当前会话隔离级别

set session transaction isolation level repeatable read;

2.5.3. 查看系统当前隔离级别

select @@global.tx_isolation;

2.5.4. 设置系统当前隔离级别

set global transaction isolation level repeatable read;

set global transaction isolation level repeatable read;

//设置read uncommitted级别：
set global transaction isolation level read uncommitted;

//设置read committed级别：
set global transaction isolation level read committed;

//设置repeatable read级别：
set global transaction isolation level repeatable read;

//设置serializable级别：
set global transaction isolation level serializable;

2.6. 查看某个用户执行过的sql语句

select * from v$sqlarea t where t.PARSING_SCHEMA_NAME in ('用户名') order by t.LAST_ACTIVE_TIME desc
用户名需要大写

select SQL_ID,PARSING_SCHEMA_NAME,module,LAST_ACTIVE_TIME,SQL_FULLTEXT from v$sqlarea order by LAST_ACTIVE_TIME desc;


oracle 查看已经执行过的sql 这些是存在共享池中的
select * from v$sqlarea t order by t.LAST_ACTIVE_TIME desc;

2.7. 会话和连接相关

2.7.1. 查看当前的连接数量和并发数

show status like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 1     |   连接数
| Threads_created   | 1     |
| Threads_running   | 1     |   当前运行的连接数（并发数）
+-------------------+-------+

show global status like '%threads_running%';

Threads_connected 跟show processlist结果相同，表示当前连接数。准确的来说，Threads_running是代表当前并发数

这是是查询数据库当前设置的最大连接数

 show variables like '%max_connections%';

可以在/etc/my.cnf里面设置数据库的最大连接数

[mysqld]
max_connections = 1000

常用的表
SELECT * FROM sys.metrics; 查看当前变量和值

2.7.2. 查找TOP sql -性能视图方式

2.7.2.1. 查找TOP sql

以下的方式仅展示数据库没有重启的这段时间的所有sql

在不方便修改客户的系统配置的前提下，我们可以直接查看如下的视图，抓出系统只能怪执行过的时间最长的sql，有些时候可能不愿意启用慢查询等配置，那么我们就以使用如下的方法分析sql
在视图sys.statement_analysis中找出总计执行时间最长的SQL语句：

select * from sys.statement_analysis limit 1\G;

视图sys.statement_analysis已经是按照总的执行时间降序排序按了，所以我们直接查询即可

还可以在视图sys.statements_with_runtimes_in_95th_percentline 中可以查询到运行时间最长的5%的语句

select * from sys.statements_with_runtimes_in_95th_percentile limit 3;

2.7.2.2. 查询平均时间最长的sql

下面的sql语句列出平均执行时间最长的语句，这类sql语句通常优化空间最大

select * from performance_schema.events_statements_summary_by_digest order by avg_timer_wait desc limIt 1 \G;

查询执行次数最多的,这种对整体信息影响较大

select * from performance_schema.events_statements_summary_by_digest order by count_star desc limit 3 \G;

检查函数最多的sql，消耗读最多

sum_row_examined

返回行数最多，消耗最多的网络带宽

sum_rows_sent 

2.7.3. 查找TOP sql -存储过程方式

存储过程

diagnostics()
ps_trace_statement_digest()
statement_performance_analyzer()
ps_trace_thread()

2.7.3.1. diagnostics()

diagnostics() 存储过会生成一个关于当前msyql整体性能的诊断报告
eg：

tee diagnostics.log
call sys.diagnostics(null,null,'current');
notee;

2.7.3.2. ps_trace_statement_digest()

2.7.3.3. statement_performance_analyzer()

2.7.3.4. ps_trace_thread()

2.8. 查看参数文件位置：

MySQL8.0中将很多数据库配置信息都写入了variables_info表中，查找起来非常方便。

仅8.0版本有改表
 select * from performance_schema.variables_info where VARIABLE_NAME='max_connections' \G

select VARIABLE_PATH,VARIABLE_SOURCE,count(*)  from performance_schema.variables_info where length(VARIABLE_PATH)!=0 group by VARIABLE_PATH,VARIABLE_SOURCE;

ps -ef|grep mysql

2.9. msyql innodb缓存池的命中率

show status like '%innodb_buffer_pool_read%';

• Innodb_buffer_pool_read_requests 逻辑读 表示向innodb缓存池进行逻辑读额次数 逻辑读请求数，但是因为一次请求一个块，所以等同于逻辑读的page数。
• Innodb_buffer_pool_reads物理读 表示从物理磁盘读取数据的次数 ：等同于物理读的page数

msyql innodb缓存池的命中率=(Innodb_buffer_pool_read_requests-Innodb_buffer_pool_reads)/Innodb_buffer_pool_read_requests*100%

对于设置innodb缓存大小的思考
1.太小的缓存池可能会导致数据页备频繁的从磁盘读取到内存，导致性能下降
2.如果设置过大，可能会造成内存被交换到swap分区，引起内存急剧下降
3.这两种情况比较起来，把innodb缓存池设置小一些对性能的负面影响不是特别大，实际生产中
mysql进程崩溃的一个常见原因是操作系统的内存耗尽（导致系统OMM），然后系统会将mysql服务kill掉

innodb_buffer_pool_size 如果是专用的mysql服务器，建议设置系统内存的70-80%左右
innodb_buffer_pool_instances表示要将缓存分成多少份，值能在innodb_buffer_pool_size>1G时生效

2.10. mysql拼接批量alert表语句

select CONCAT("alter table ",table_name," modify column ",column_name," datetime;")
from information_schema.columns 
where table_schema='pcard' and data_type='datetime'
limit 10;


select CONCAT("alter table ",table_name," modify column ",column_name," int;")
from information_schema.columns 
where table_schema='pcard' and data_type='decimal'
limit 10;

2.11. mysql 替换字段中的换行符

select REPLACE(age,CHAR(10),'') from testa;

CHAR(10) 表示的就是换行符

2.12. 查询mysql自增主键使用情况

SELECT table_schema,
       TABLE_NAME,
       COLUMN_NAME,
       AUTO_INCREMENT ,
       round((AUTO_INCREMENT/(POW(2, CASE data_type WHEN 'tinyint' THEN 7 WHEN 'smallint' THEN 15 WHEN 'mediumint' THEN 23 WHEN 'int' THEN 31 WHEN 'bigint' THEN 63 END+(column_type LIKE '% unsigned'))))*100,0) as USED_PERCENT,
       POW(2, CASE data_type WHEN 'tinyint' THEN 7 WHEN 'smallint' THEN 15 WHEN 'mediumint' THEN 23 WHEN 'int' THEN 31 WHEN 'bigint' THEN 63 END+(column_type LIKE '% unsigned')) AS MAX_VALUES,
       COLUMN_TYPE
FROM information_schema.tables t
JOIN information_schema.columns c USING (table_schema,
                                         TABLE_NAME)
WHERE c.extra = 'auto_increment'
  AND t.TABLE_SCHEMA NOT IN ('information_schema',
                             'mysql',
                             'sys',
                             'performance_schema')
AND t.auto_increment IS NOT NULL 
and t.TABLE_NAME in('saas_company_song','saas_company_song_new');

SELECT
 table_schema,
 table_name,
 column_name,
 AUTO_INCREMENT,
 POW(2, CASE data_type
   WHEN 'tinyint'  THEN 7
   WHEN 'smallint' THEN 15
   WHEN 'mediumint' THEN 23
   WHEN 'int'    THEN 31
   WHEN 'bigint'  THEN 63
   END+(column_type LIKE '% unsigned'))-1 AS max_int 
  FROM information_schema.tables t
   JOIN information_schema.columns c USING (table_schema,table_name)
  WHERE
   c.extra = 'auto_increment'
  AND
   t.TABLE_SCHEMA NOT IN ('information_schema','mysql', 'sys','test','performance_schema') 
  AND
   t.auto_increment IS NOT NULL ;

修改数据库和表的字符集：

修改数据库的字符集需要重启才能生效
alter database kettle character set utf8mb4;

ALTER TABLE r_cluster DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

查看表和列的注释：

查看表的注释：
select TABLE_SCHEMA,table_name,TABLE_COMMENT from information_schema.TABLES ;

查看列的注释：
SELECT 
  a.table_name 表名,
  a.table_comment 表说明,
  b.COLUMN_NAME 字段名,
  b.column_comment 字段说明,
  b.column_type 字段类型,
  b.column_key 约束 
FROM
  information_schema.TABLES a 
  LEFT JOIN information_schema.COLUMNS b 
    ON a.table_name = b.TABLE_NAME 
WHERE a.table_schema = '数据库名' 
ORDER BY a.table_name 

2.13. mysql新增列，并将该列放到指定列之后

ALTER TABLE `goldkinn_center_test_salary`.`year_result_info_new` 
ADD COLUMN `next_use_year` int(4) NULL AFTER `description`;

2.14. mysql临时表解决create table x select 字段1，字段2 from tab

创建临时表：
CREATE TEMPORARY TABLE  select id from test;

查看临时表表结构
show create table Sale;

CREATE TEMPORARY TABLE `sale` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

通过临时表创建表结构
create table xxxx like Sale;

insert 数据
insert into xxx select id from test;

2.15. mysql 手动锁表操作：

begin;

LOCK TABLES in_prodcost_config WRITE,in_prodcost_price WRITE;
alter table in_prodcost_config add column fine_org_id bigint(32) DEFAULT NULL comment '精细库存组织ID' after `bom_id`;

alter table in_prodcost_price add column fine_org_id bigint(32) DEFAULT NULL comment '精细库存组织ID' after `organization_id`;

UNLOCK TABLES;
commit;


oracle:

SET TRANSACTION;

LOCK TABLE er_bxzb in SHARE MODE;
LOCK TABLE er_jkzb in SHARE MODE;

alter table er_bxzb modify zy varchar2(1500);
alter table er_jkzb modify zy varchar2(1500);

commit;

2.16. 添加函数索引

alter table amb_data_result_accounts add index fun_idx_fm_date((DATE_FORMAT(fm_date,_utf8mb3'%Y-%m-%d %H:%i:%s')));

alter table amb_data_docs add index fun_idx_fm_date((DATE_FORMAT(fm_date,_utf8mb3'%Y-%m-%d %H:%i:%s')));

线上无锁添加索引：加索引的语句不加锁

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

说明

更优秀的解决方案，在当前表加索引，步骤：
ALGORITHM=INPLACE
1.创建索引(二级索引)数据字典
2.加共享表锁，禁止DML，允许查询
3.读取聚簇索引，构造新的索引项，排序并插
入新索引
4.等待打开当前表的所有只读事务提交
5.创建索引结束
 
ALGORITHM=COPY
通过临时表创建索引，需要多一倍存储，还有更多的IO，步骤：
1.新建带索引（主键索引）的临时表
2.锁原表，禁止DML，允许查询
3.将原表数据拷贝到临时表
4.禁止读写,进行rename，升级字典锁
5.完成创建索引操作
 
LOCK=DEFAULT：默认方式，MySQL自行判断使用哪种LOCK模式，尽量不锁表
LOCK=NONE：无锁：允许Online DDL期间进行并发读写操作。如果Online DDL操常用查询
作不支持对表的继续写入，则DDL操作失败，对表修改无效
LOCK=SHARED：共享锁：Online DDL操作期间堵塞写入，不影响读取
LOCK=EXCLUSIVE：排它锁：Online DDL操作期间不允许对锁表进行任何操作

查询视图的定义者

select * from information_schema.VIEWS where DEFINER='root@%';
select distinct DEFINER from information_schema.VIEWS;

批量修改语句
select concat("alter DEFINER='root'@'172.28.1.%' SQL SECURITY DEFINER VIEW ",TABLE_SCHEMA,".",TABLE_NAME," as ",VIEW_DEFINITION,";") from information_schema.VIEWS where DEFINER = "root@%";

5.7修改函数定义者

mysql.proc

mysql 8.0统一修改函数定义者

SELECT * FROM information_schema `ROUTINES` 
select DEFINER from mysql.proc where DEFINER='gkdb-bpm-prod-web@%'
select dinstinct DEFINER from mysql.proc;

查看mysql中库下面的所有索引和字段信息

拼接重建索引语句

SELECT a.TABLE_SCHEMA,
a.TABLE_NAME,
a.index_name,
GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`
FROM information_schema.statistics a
where a.TABLE_SCHEMA='goldkinn_mes_pre'
GROUP BY a.TABLE_SCHEMA,a.TABLE_NAME,a.index_name

拼接重建索引语句
SELECT
CASE b.index_name
	WHEN 'PRIMARY' THEN 
		  CONCAT('alter table ',b.TABLE_SCHEMA,'.',b.TABLE_NAME,' drop ',b.index_name,' key ;')
			ELSE 
			CONCAT('alter table ',b.TABLE_SCHEMA,'.',b.TABLE_NAME,' drop INDEX ',b.index_name,';')
			END as delete_idx,
			CASE b.index_name
	WHEN 'PRIMARY' THEN 
		  CONCAT('alter table ',b.TABLE_SCHEMA,'.',b.TABLE_NAME,' add ',b.index_name,'key (',b.Columns,');')
			ELSE 
			CONCAT('alter table ',b.TABLE_SCHEMA,'.',b.TABLE_NAME,' add INDEX ',b.index_name,'(',b.Columns,');')
			END as add_idx
		FROM
			(
			SELECT
				a.TABLE_SCHEMA,
				a.TABLE_NAME,
				a.index_name,
				GROUP_CONCAT( column_name ORDER BY seq_in_index ) AS `Columns` 
			FROM
				information_schema.statistics a 
			WHERE
				a.TABLE_SCHEMA = 'goldkinn_mes_pre' 
			GROUP BY
				a.TABLE_SCHEMA,
				a.TABLE_NAME,
			a.index_name 
	) AS b