mysql的常用命令(持续更新)

• docker中启动mysql数据库命令行

# docker exec -it mysql bash

root@f7ae06840549:/# mysql -uroot -proot

• 显示所有数据库

show databases;

• 创建数据库

create database 数据库名;

• 选择数据库

use 数据库名；

• 创建表

create table sys_user (
  id int(11) auto_increment
  ,username varchar(12) not null
  ,password varchar(120) not null
  ,status int(1)
)ENGINE=MyISAM AUTO_INCREMENT=1;

ENGINE是指定表所使用的引擎

innodb 行锁

MyISAM 表锁

AUTO_INCREMENT是指定自增的步长

• 查看数据库字符集

show variables like '%char%';

• 查看数据库引擎

show engines;

• 查看当前使用的引擎

show variables like '%storage_engine%';

• 创建单值索引

create index dept_index on tb(dept);

• 创建唯一索引

create unique index name_index on tb(name);

• 创建复合索引

create index dept_name_index on tb(dept, name);

• alter方式创建单值索引

alter table tb add index dept_index(dept);

• alter方式创建唯一索引

alter table tb add unique index name_index(name);

• alter方式创建复合索引

alter table tb add index dept_name_index(dept, name);

• 删除索引

drop index name_index on tb;

• 查询表索引

show index from tb \G;

\G为我们格式化显示

• sql执行计划

explain select * from tb;

• 添加主键

alter table tb add constraint tid_pk primary key(tid);

• 删除主键

alter table tb drop primary key;

• 显示建表语句

show create table tb;

• 查看表结构

desc tb;

• 添加字段

alter table tb add column name char(20);

• 查看排序长度限制即buffer的容量大小

SHOW VARIABLES LIKE '%max_length_for_sort_data%';

• 设置排序长度限制即buffer的容量大小

set max_length_for_sort_data=1024

• 查看慢查询日志是否开启

SHOW VARIABLES LIKE '%slow_query_log%';

• 临时开启慢查询日志

set global slow_query_log = 1;

永久开启的话修改mysql的配置

my.cnf

[mysqld]
slow_query_log=1
slow_query_log_file=/usr/local/mysql/logs/localhost-slow.log
long_query_time=3

查看日志

mysqldumpslow -s t -t 10 -g "left join" localhost-slow.log

• 查看慢查询阈值

SHOW VARIABLES LIKE '%long_query_time%';

• 修改慢查询阈值

set global long_query_time = 5;

• 查看超过阈值的SQL文有几个

SHOW GLOBAL STATUS LIKE '%slow_queries%';

• 解决存储过程/函数和开启慢查询日志冲突

SHOW VARIABLES LIKE '%log_bin_trust_function_creators%';

set global log_bin_trust_function_creators = 1;

• 查看profiles是否开启

show variables like '%profiling%';

• 开启profiles，开启后，记录sql的所有语句

set profiling = on;

• 查看所有sql语句，通过profiles

show profiles;

• 查看profiles中，sql具体的消耗

show profile all for query id

show profile cpu,block io for query id

• 查看全局查询日志是否开启

show variables like '%general_log%';

• 开启全局查询日志

set global general_log =1;

set global log_output='table';

还可以指定文件

set global log_output='file';

set global general_log =on;

set global general_log_file=/tmp/test.log

• 查看加锁的表

show open tables;

• 给表加读锁

lock table 表名 read;

• 给表加写锁

lock table 表名 write;

• 给表解锁

unlock table;

• 查看表的状态

show status like 'table%';

这里结果

一般建议，Table_locks_immediate/Table_locks_waited>5000建议使用InnoDB引擎，否则使用MyISAM引擎

• 关闭自动提交

set autocommint =0;

• 查看innodb引擎的锁状况

show status like '%innodb_row_lock%';

• 开启事务

begin；

start transaction;