mysql 常见操作指令

use k_order

– 查看版本
select version();

– 查看所有数据库
show databases;

– 查看所有执行引擎
show engines;

– 查看当前数据库
select database();

– 查看所有table
show tables;

– 查看默认存储引擎
SHOW VARIABLES LIKE ‘default_storage_engine’;
– 系统变量分为两种 SESSION和GLOBAL； GLOBAL 是全局的，而 SESSION 只针对当前回话窗口。
– 查询SESSION范围系统变量
SHOW SESSION VARIABLES LIKE ‘default_storage_engine’;
– 查询GLOBAL范围系统变量
SHOW GLOBAL VARIABLES LIKE ‘default_storage_engine’;
– 设置SESSION范围系统变量
SET SESSION default_storage_engine = MyISAM;
– 设置GLOBAL范围级别系统变量
set global transaction isolation level REPEATABLE READ; – GLOBAL级别需要新开会生效；SESSION级别当前会话就生效.

– 查看最大的连接
SHOW VARIABLES like ‘max_connections’;

– 查看用户正在运行的其他线程，root能看到所有，其他用户只能看到自己除非赋予process权限
SHOW PROCESSLIST;
SHOW full PROCESSLIST;

– 查看隔离级别 mysql5.7及之后版本
SHOW VARIABLES LIKE ‘transaction_isolation’;
select @@global.transaction_isolation,@@transaction_isolation;

– 查看隔离级别 mysql5.7之前版本
show variables like ‘tx_isolation’;
select @@global.tx_isolation,@@tx_isolation;

– 查询当前执行事务
select * from information_schema.innodb_trx;
– 查看锁
select * from information_schema.INNODB_LOCKS;
– 查看锁等待
select * from information_schema.INNODB_LOCK_WAITS

– 字符集和比较规则

– 如果创建或修改列时没有显式的指定字符集和比较规则，则该列默认用表的字符集和比较规则
– 如果创建或修改表时没有显式的指定字符集和比较规则，则该表默认用数据库的字符集和比较规则
– 如果创建或修改数据库时没有显式的指定字符集和比较规则，则该数据库默认用服务器的字符集和比较规则
– 服务器级别字符集
show variables like ‘character_set_server’;
– 服务器级别比较规则
show variables like ‘collation_server’;

– 数据库级别字符集
show variables like ‘character_set_database’;
– 数据库级别比较规则 【utf8mb4_general_ci：case insensitive】
show variables like ‘collation_database’;
– 建表/修改表时指定字符集和比较规则 create table t( col varchar(10) ) character set utf8 collate utf8_general_ci; alter table t modify col varchar(10) character set gbk collate gbk_chinese_ci; SHOW tables;

– 服务器解码请求时使用的字符集 character_set_client
SHOW VARIABLES LIKE ‘character_set_client’;

– 服务器处理请求时会把请求字符串从 character_set_client 转为 character_set_connection
SHOW VARIABLES LIKE ‘character_set_connection’;

– 服务器向客户端返回数据时使用的字符集character_set_results
SHOW VARIABLES LIKE ‘character_set_results’;

– 这三个一般都设置成相同的
SET NAMES utf8;
– 这一条语句产生的效果和我们执行这3条的效果是一样的：
SET character_set_client = utf8;
SET character_set_connection = utf8;
SET character_set_results = utf8;

– 建表

CREATE TABLE `order` (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `code` varchar(255) DEFAULT NULL COMMENT '订单code',
  userId int(11) DEFAULT NULL COMMENT '用户id',
	productId int(11) DEFAULT NULL COMMENT '产品id',
	count varchar(255) DEFAULT NULL COMMENT '产品数量',
  create_time datetime NOT NULL default CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time datetime NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='订单主表';

– 建存储过程(常用来生成测试数据)

DELIMITER $$ 
create procedure insert_order(n int)
begin
declare i int default 1094;
set autocommit = 0;
repeat
set i = i + 1;
insert into k_order.`order`(`code`,`userId`,`productId`) values( i, i,i);
until i=n  
end repeat;
commit;
set autocommit = 1;
end $$ 
-- 调用存储过程
call insert_order(2000000)

– 查看索引击中
explain select * from k_order.user where number = 10000 and age > 17

– 共享锁(读锁)

SHOW VARIABLES LIKE 'autocommit';
SET autocommit = 0;
begin;
select * from k_order.`user` lock in share mode;
select * from k_order.test1;
commit;

– 排他锁(写锁)

SET autocommit = 0;
begin;
select * from k_order.`user` where id =1 for update;
select * from k_order.test1;
commit;

– 查看是否开启慢查询
SHOW VARIABLES LIKE ‘slow%’
– 查看慢查询设置的时间
SHOW VARIABLES LIKE ‘long%’

– 查看保存方式 建议：file
show variables like ‘%log_output%’; – talbe – file

– 慢查询设置

vi /etc/my.cnf

slow_query_log=ON
slow_query_log_file=/var/lib/mysql/localhost-centos-slow.log
long_query_time=3

– 得到返回记录集最多的10个SQL。
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
– 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

– 取出查询时间最慢的三个查询
mysqldumpslow -s t -t 3 /var/run/mysqld/mysqld-slow.log
mysqldumpslow -a -s t -t 3 /var/run/mysqld/mysqld-slow.log -a可以显示查询时间