MySQL 常用命令

snap connect mysql-workbench-community
snap connect mysql-workbench-community:password-manager-service
snap connect mysql-workbench-community:ssh-keys

-- 更改数据库字符集
ALTER DATABASE [database_name]
  CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- 更改表字符集
ALTER TABLE [table_name]
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Change a column
ALTER TABLE [table_name]
  CHANGE [column_name] [column_name] VARCHAR(255)
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 调整表字段顺序
ALTER TABLE [table_name]
  CHANGE COLUMN `x1` `x1` LONGTEXT NULL DEFAULT NULL AFTER `x2`;

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name [, tbl_name] ...

- 一般输出 Table does not support optimize, doing recreate + analyze instead，等价于 `alter table <table_name> ENGINE=InnoDB`

# rename
rename table <old-table-name> to <new-table-name>;

# aalter
alter table <old-table-name> rename as <new-table-name>;

JSON_EXTRACT(json_doc, path[, path] …)

# 创建数据库
create database test;
use test

# 创建表
CREATE TABLE `json_table` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `value` json DEFAULT NULL COMMENT 'json value',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 表结构
mysql> desc json_table;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | bigint(20) | NO   | PRI | NULL    | auto_increment |
| value | json       | YES  |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> show create table json_table;
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| json_table | CREATE TABLE `json_table` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `value` json DEFAULT NULL COMMENT 'json value',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

# 插入数据
insert into `json_table` values (1, '{"name": "xie", "age": 18}');
insert into `json_table` values (2, '{"name": "xianbin", "site": "https://www.xiexianbin.cn"}');

# 查询所有字段
mysql> select * from json_table;
+----+----------------------------------------------------------+
| id | value                                                    |
+----+----------------------------------------------------------+
|  1 | {"age": 18, "name": "xie"}                               |
|  2 | {"name": "xianbin", "site": "https://www.xiexianbin.cn"} |
+----+----------------------------------------------------------+
2 rows in set (0.00 sec)

# 简单示例
mysql> select json_extract('{"name":"xianbin","age":"18"}', "$.age");
+-------------------------------------------------------+
| json_extract('{"name":"xianbin","age":"18"}',"$.age") |
+-------------------------------------------------------+
| "18"                                                  |
+-------------------------------------------------------+
1 row in set (0.00 sec)

# 查询 json 的 name 字段
mysql> select json_extract(`value`, '$.name') from `json_table`;
+---------------------------------+
| json_extract(`value`, '$.name') |
+---------------------------------+
| "xie"                           |
| "xianbin"                       |
+---------------------------------+
2 rows in set (0.00 sec)

# 默认值为 NULL
mysql> select json_extract(`value`, '$.name') as `name`, json_extract(`value`, '$.site') as `site` from `json_table`;
+-----------+-----------------------------+
| name      | site                        |
+-----------+-----------------------------+
| "xie"     | NULL                        |
| "xianbin" | "https://www.xiexianbin.cn" |
+-----------+-----------------------------+
2 rows in set (0.00 sec)

# 数组示例
mysql> insert into `json_table` values (3, '[{"name": "xiexianbin", "site": "https://www.xiexianbin.cn"}]');

# 查询数据的内容
mysql> select json_extract(`value`, '$[0].name') from `json_table` where id = 3;
+------------------------------------+
| json_extract(`value`, '$[0].name') |
+------------------------------------+
| "xiexianbin"                       |
+------------------------------------+
1 row in set (0.00 sec)

# json 查询条件
mysql> select * from `json_table` where json_extract(`value`, '$.name') = 'xianbin';
+----+----------------------------------------------------------+
| id | value                                                    |
+----+----------------------------------------------------------+
|  2 | {"name": "xianbin", "site": "https://www.xiexianbin.cn"} |
+----+----------------------------------------------------------+
1 row in set (0.00 sec)

SELECT select_list
FROM <table_name>
USE INDEX(<index1, index2, ...>)
-- IGNORE
-- FORCE INDEX
WHERE <condition>;

SHOW INDEXES FROM <table_name>;

show processlist;

# 等价于
select * from information_schema.processlist where User='UserName';

# 生成杀死进程的命令
select concat('kill ',ID,';') from information_schema.processlist where User='UserName';

# 监控统计每个用户的访问
select User,count(*) as cnt from information_schema.processlist group by user;

mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info             |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL | Daemon  |   28 | Waiting on empty queue | NULL             |
| 10 | root            | localhost | NULL | Query   |    0 | init                   | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
2 rows in set (0.00 sec)

select * from information_schema.processlist;
select * from information_schema.processlist where User='UserName';

# 生成 kill 的命令
select concat('kill ',ID,';') from information_schema.processlist where User='UserName';

# 统计用户的连接数量
select User, count(*) as cnt from information_schema.processlist group by user;

[root@xiexianbin_cn ~]# cat .my.cnf
[client]
user=root
password=pass

mysql -uroot -S /var/lib/mysql/mysql.sock

# 赋权
GRANT privileges (columns)
    ON what
    TO user IDENTIFIED BY "password"
    WITH GRANT OPTION

# 写入、刷新权限
FLUSH PRIVILEGES;

# 查询
show grants for <user>;

GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'root';
GRANT ALL ON *.* TO 'root'@'localhost' IDENTIFIED BY 'root' WITH GRANT OPTION;
FLUSH PRIVILEGES;

GRANT ALL ON test.* TO 'root'@'%' IDENTIFIED BY 'test';
GRANT ALL ON test.* TO 'root'@'localhost' IDENTIFIED BY 'test' WITH GRANT OPTION;
FLUSH PRIVILEGES;

GRANT SElECT ON test.* TO 'root'@'${ip}' IDENTIFIED BY "test";
FLUSH PRIVILEGES;

# 权限回收
REVOKE all ON *.* FROM 'root'@'localhost';

show grants for itat@‘%';

mysql -uroot -proot -Dmysql -e "select host,user from user";

for name in a b c; do
  mysql -uroot -proot -Dmysql -e"CREATE DATABASE IF NOT EXISTS ${name}_t DEFAULT CHARSET utf8 COLLATE utf8_general_ci;";
  mysql -uroot -proot -Dmysql -e"CREATE DATABASE IF NOT EXISTS ${name}_o DEFAULT CHARSET utf8 COLLATE utf8_general_ci;";
done

mysql -uroot -proot -e "show databases"

drop database data1;

CREATE TABLE training(
  id INT(11) PRIMARY KEY,
  col1 VARCHAR(20) NOT NULL,
  START DATE);

mysql> show create table t_config \G
*************************** 1. row ***************************
       Table: t_config
Create Table: CREATE TABLE `t_config` (
  `id` int(11) NOT NULL,
  `col2` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

show variables like '%datadir%';

show variables like '%version%';

show variables like 'expire_log%';
set global expire_logs_days=7;
select @@expire_logs_days;

show engines;

show engine innodb status;

create table hpc_job_table_2021_1231 like hpc_job_table;
insert into hpc_job_table_2021_1231 select * from hpc_job_table where time_end < "2021-12-31";
delete from hpc_job_table where time_end < "2021-12-31";

select
  table_schema as '数据库',
  table_name as '表名',
  table_rows as '记录数',
  truncate(data_length/1024/1024, 2) as '数据容量(MB)',
  truncate(index_length/1024/1024, 2) as '索引容量(MB)'
  from information_schema.tables
  where table_schema='mysql'
  order by data_length desc, index_length desc;

select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB, concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB
  from tables
  where table_schema='passport' and table_name='tb_user_info';

innochecksum --count <table>.ibd  # ibd 文件中共有多少个page
innochecksum --page-type-summary sbtest1.ibd
innochecksum --page-type-dump=/tmp/<table>.log sbtest1.ibd

SELECT
    CONCAT(
        'create user \'',
    user,
    '\'@\'',
    Host,
    '\''
    ' IDENTIFIED BY PASSWORD \'',
    authentication_string,
        '\';'
    ) AS CreateUserQuery
FROM
    mysql.`user`
WHERE
    `User` NOT IN (
        'mysql.session',
        'mysql.sys'
    );

#!/bin/bash
#Function export user privileges

pwd=root
expgrants()
{
  mysql -B -u'root' -p${pwd} -N $@ -e "SELECT CONCAT(  'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
  mysql -u'root' -p${pwd} $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'
}

expgrants > /tmp/grants.sql
echo "flush privileges;" >> /tmp/grants.sql

#执行脚本后结果
-- Grants for read@%
GRANT SELECT ON *.* TO 'read'@'%';

-- Grants for root@%
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

-- Grants for test@%
GRANT USAGE ON *.* TO 'test'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, EXECUTE, CREATE VIEW, SHOW VIEW ON `test_db`.* TO 'test'@'%';

-- Grants for test_user@%
GRANT USAGE ON *.* TO 'test_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON `test_db`.`t1` TO 'test_user'@'%';

-- Grants for mysql.session@localhost
GRANT SUPER ON *.* TO 'mysql.session'@'localhost';
GRANT SELECT ON `performance_schema`.* TO 'mysql.session'@'localhost';
GRANT SELECT ON `mysql`.`user` TO 'mysql.session'@'localhost';

-- Grants for mysql.sys@localhost
GRANT USAGE ON *.* TO 'mysql.sys'@'localhost';
GRANT TRIGGER ON `sys`.* TO 'mysql.sys'@'localhost';
GRANT SELECT ON `sys`.`sys_config` TO 'mysql.sys'@'localhost';

# 对库生效
FLUSH TABLES WITH READ LOCK;
UNLOCK TABLES;

# 对表生效
LOCK TABLES table_name READ;