mysql alter atafter_MySQL 常用操作

库和表

删除库

drop database 数据库名;

修改库名称

比如源库名称为 db1，要改为 db2.

2种方式：

mysqldump 先导出db1，再导入到db2

mysqldump -uroot -p'password' --default-character-set=utf8 -R --triggers --events --opt --set-gtid-purged=off --hex-blob --single-transaction --master-data=2 db1>db1_export.sql

mysql>create databse db2;

mysql>use db2;

mysql>source db1_export.sql;

新建库，然后将 db1的所有表 rename 到db2，操作如下：

create database db2;

rename db1.user to db2.user;

rename db1.xxx to db2.xxx;

...

这种速度比较快，可以用脚本循环 rename 表名

删除表

drop table 表名;

表字段

desc 表

查看所有表字段

show create table 表

查看建表语句

添加字段

# 添加主键

alter table user add column id bigint(11) not Null auto_increment primary key;

# 在开头添加 first

alter table user add column address varchar(64) not null first;

# 在 sex 字段后面添加 after sex

alter table user add column address varchar(64) not null after sex;

修改字段

alter table user modify column sex tinyint(1) default 0 after name;

删除字段

alter table user drop column address;

json 字段(5.7及以上)

建表

CREATE TABLE `tb_json` (

`id` BIGINT(11) NOT NULL AUTO_INCREMENT,

`content` JSON NULL DEFAULT NULL,

PRIMARY KEY (`id`)

);

添加值

INSERT into tb_json(content) VALUES ('{"id":1,"name":"zhangsan","sex":"男"}');

更新值

update tb_json set content=JSON_SET(content, '$.sex', "女") where id=1;

查询

mysql> SELECT content,JSON_EXTRACT(content, '$.sex') from tb_json;

+---------------------------------------------+--------------------------------+

| content | JSON_EXTRACT(content, '$.sex') |

+---------------------------------------------+--------------------------------+

| {"id": 1, "sex": "女", "name": "zhangsan"} | "女" |

+---------------------------------------------+--------------------------------+

索引

添加索引

# 主键索引

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` );

# 普通索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column` );

# 唯一索引

ALTER TABLE `table_name` ADD UNIQUE INDEX index_name ( `column` );

# 复合索引

ALTER TABLE `table_name` ADD INDEX index_name( `column1`, `column2`, `column3`);

删除索引

ALTER TABLE user DROP INDEX idx_name;

修改索引

先删除，再新建

ALTER TABLE `user` DROP INDEX idx_name;

ALTER TABLE `user` ADD INDEX idx_name(`name`);

连接数

查看、设置最大连接数

查看

show variables like 'max_connections';

设置

set GLOBAL max_connections = 10240;

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

[mysqld]

max_connections = 10240

当前占用连接

root 用户能看到所有

其他用户只能看到自己的

show processlist; # 100条

show full processlist; # 所有

其他

show status like '%变量%';

变量

解释

Aborted_clients

由于客户没有正确关闭连接已经死掉，已经放弃的连接数量

Aborted_connects

尝试已经失败的MySQL服务器的连接的次数

Connections

试图连接MySQL服务器的次数

Created_tmp_tables

当执行语句时，已经被创造了的隐含临时表的数量

Delayed_insert_threads

正在使用的延迟插入处理器线程的数量

Delayed_writes

用INSERT DELAYED写入的行数

Delayed_errors

用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数

Flush_commands

执行FLUSH命令的次数

Handler_delete

请求从一张表中删除行的次数

Handler_read_first

请求读入表中第一行的次数

Handler_read_key

请求数字基于键读行

Handler_read_next

请求读入基于一个键的一行的次数

Handler_read_rnd

请求读入基于一个固定位置的一行的次数

Handler_update

请求更新表中一行的次数

Handler_write

请求向表中插入一行的次数

Key_blocks_used

用于关键字缓存的块的数量

Key_read_requests

请求从缓存读入一个键值的次数

Key_reads

从磁盘物理读入一个键值的次数

Key_write_requests

请求将一个关键字块写入缓存次数

Key_writes

将一个键值块物理写入磁盘的次数

Max_used_connections

同时使用的连接的最大数目

Not_flushed_key_blocks

在键缓存中已经改变但是还没被清空到磁盘上的键块

Not_flushed_delayed_rows

在INSERT DELAY队列中等待写入的行的数量

Open_tables

打开表的数量

Open_files

打开文件的数量

Open_streams

打开流的数量(主要用于日志记载)

Opened_tables

已经打开的表的数量

Questions

发往服务器的查询的数量

Slow_queries

要花超过long_query_time时间的查询数量

Threads_connected

当前打开的连接的数量

Threads_running

不在睡眠的线程数量

Uptime

服务器工作了多少秒