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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

– 查看索引击中
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;