MySQL汇总

2020-08-08站长阅读(968)评论(0)
Warning: A non-numeric value encountered in /data/wangzhan/tech.souyunku.com.wp/wp-content/themes/dux/functions-theme.php on line 1154
赞(77)

set @user = 123456; set @group = (select GROUP from USER where User = @user); select * from USER where GROUP = @grou...

2020-08-08站长阅读(530)评论(0)
Warning: A non-numeric value encountered in /data/wangzhan/tech.souyunku.com.wp/wp-content/themes/dux/functions-theme.php on line 1154
赞(93)

从5、7.11开始，mysql开始支持物理表空间的加密，它使用两层加密架构。 包括：master key 和 tablespace key master key用于加密tablespace key，加密后的结果存储在tablespace的h...

2020-08-08站长阅读(511)评论(0)
Warning: A non-numeric value encountered in /data/wangzhan/tech.souyunku.com.wp/wp-content/themes/dux/functions-theme.php on line 1154
赞(74)

数据库运行一段时间后，有可能会有磁盘磁片产生，此时我们需要进行optimize table操作 # 获取需要optimize的表：如下为获取总大小小于80G的表进行操作： mysql -utroot -S /tmp/mysql_33...

2020-08-08站长阅读(967)评论(0)
Warning: A non-numeric value encountered in /data/wangzhan/tech.souyunku.com.wp/wp-content/themes/dux/functions-theme.php on line 1154
赞(98)

时常有一些烂sql跑在数据库里，我们要进行kill，避免影响拖垮数据库。 mysql> show processlist; +—-+——+————&...

2020-08-08站长阅读(927)评论(0)
Warning: A non-numeric value encountered in /data/wangzhan/tech.souyunku.com.wp/wp-content/themes/dux/functions-theme.php on line 1154
赞(79)

库的字符集： SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = R...

2020-08-08站长阅读(991)评论(0)
Warning: A non-numeric value encountered in /data/wangzhan/tech.souyunku.com.wp/wp-content/themes/dux/functions-theme.php on line 1154
赞(88)

— 计算MySQL的内存峰值公式，计算所有的连接满了的情况下： select (@@key_buffer_size + @@query_cache_size + @@tmp_table&#...

2020-08-08站长阅读(916)评论(0)
Warning: A non-numeric value encountered in /data/wangzhan/tech.souyunku.com.wp/wp-content/themes/dux/functions-theme.php on line 1154
赞(69)

show variables 会存在数据被截断的问题： select 全局变量没有问题 官网解释：https://dev.mysql.com/doc/refman/5.6/en/variables-table.html The VARIAB...

2020-08-08站长阅读(929)评论(0)
Warning: A non-numeric value encountered in /data/wangzhan/tech.souyunku.com.wp/wp-content/themes/dux/functions-theme.php on line 1154
赞(84)

select a.TABLE_SCHEMA,a.TABLE_NAME from information_schema.`COLUMNS` a left join (select ‘etl&...

2020-08-08站长阅读(951)评论(0)
Warning: A non-numeric value encountered in /data/wangzhan/tech.souyunku.com.wp/wp-content/themes/dux/functions-theme.php on line 1154
赞(86)

SELECT a.TABLE_SCHEMA, a.TABLE_NAME, a.index_name, GROUP_CONCAT(column_name ORDER BY seq_in_...

2020-08-08站长阅读(950)评论(0)
Warning: A non-numeric value encountered in /data/wangzhan/tech.souyunku.com.wp/wp-content/themes/dux/functions-theme.php on line 1154
赞(61)

在从库进行锁表操作flush table with read lock， 通过show slave status\G 获取对应主库的位置点： show slave status\G ****&...