[数据库] MySQL之数据库管理篇

管理数据库

0 `mysql`命令行

-h, --host=name     Connect to host.
-P, --port=#        Port number to use for connection or 0 for default to, in
                    order of preference, my.cnf, $MYSQL_TCP_PORT,
                    /etc/services, built-ixn default (3306).
-u, --user=name     User for login if not current user.
-p, --password[=name] 
                    Password to use when connecting to server. If password is
                    not given it's asked from the tty.

-v, --verbose       Write more. (-v -v -v gives the table output format).
-V, --version       Output version information and exit.

-e, --execute=name  Execute command and quit. (Disables --force and history
                    file.)

-U, --safe-updates  Only allow UPDATE and DELETE that uses keys.
-U, --i-am-a-dummy  Synonym for option --safe-updates, -U.

1 mysql连接/开闭/版本篇

安装客户端

Ubuntu

apt-get install mysql-client-core-8.0

连接

mariadb在Linux中首次进入mysql（因为此时还没有创建任何用户，mysql的root并不等效于linux中的root用户）

sudo mysql

进入/连接mysql（创建数据库用户后）

[本地登录]
mysql -u userName -p

[远程登录]
mysql -u<userName> [-h<hostName>] -p<password>

[补充: 指定端口等连接信息]
--[case1]--
mysql  -h127.0.0.1 --port=3306 -uroot -p
--[case2]--
mysql -uroot -pMyPassword --port=3306 -h127.0.0.1 -e 'show databases;'

查看数据库连接的所有进程

SHOW FULL PROCESSLIST

Id	User	Host	Command	Time	State	Info	Memory_Used	Memory_used_by_user
46402836	cdc	172.xx.222.xxx:48722	Binlog Dump GTID	4665167	Master has sent all binlog to slave; waiting for more updates		14038	8208
46402839	cdc	172.xx.222.xxx:48728	Binlog Dump GTID	4665168	Master has sent all binlog to slave; waiting for more updates		13798	8208
52530737	biz_test	10.xx.37.xxx:64512	Query	0	starting/executing	/* ApplicationName=DBeaver 23.0.4 - SQLEditor <Script-304.sql> */ SHOW FULL PROCESSLIST	128018	9144

批量杀掉连接进程

[方式1](亲测有效)通过information_schema.processlist表中的连接信息生成需要处理掉的MySQL连接的语句临时文件，然后执行临时文件中生成的指令
mysql> select concat('KILL ',id,';') from information_schema.processlist where user='root';
+------------------------+
| concat('KILL ',id,';') |
+------------------------+
| KILL 3101;             |
| KILL 2946;             |
+------------------------+
2 rows in set (0.00 sec)

mysql>select concat('KILL ',id,';') from information_schema.processlist where user='root' into outfile '/tmp/a.txt';
Query OK, 2 rows affected (0.00 sec)
 
mysql>source /tmp/a.txt;
Query OK, 0 rows affected (0.00 sec)

[方式2]
mysqladmin -uroot -p processlist|awk -F "|" '{print $2}'|xargs -n 1 mysqladmin -uroot -p kill

mysqladmin -uroot -p processlist|awk -F "|" '{if($3 == "Mike")print $2}'|xargs -n 1 mysqladmin -uroot -p kill

[方式3] 通过SHEL脚本实现
#杀掉锁定的MySQL连接
for id in `mysqladmin processlist|grep -i locked|awk '{print $1}'`
do
   mysqladmin kill ${id}
done

[方式4] 通过Maatkit工具集中提供的mk-kill命令进行
#杀掉超过60秒的sql
mk-kill -busy-time 60 -kill
#如果你想先不杀，先看看有哪些sql运行超过60秒
mk-kill -busy-time 60 -print
#如果你想杀掉，同时输出杀掉了哪些进程
mk-kill -busy-time 60 -print –kill

查看mysql的最大连接数

show variables like '%max_connections%';

查看服务器响应的最大连接数

 show global status like 'Max_used_connections';

查看数据库开放的端口

[方式1]

shell> mysql -uroot -pPasswd
mysql> show global variables like 'port';

[方式2]

shell> ps -ef|grep mysql
shell> cat /home/sdc/public/app/mysql/conf/my.cnf | grep -i port
port=53302

[方式3]

mysql> netstat -antp | grep -i mysql

运行/开闭

查看mysql是否已经在运行

## Linux 系统
ps -ef | grep mysqld

启动mysql

## Windows 系统
#### 方式1
step1: 我的电脑(右键)>管理>服务和应用程序>服务
       或者 Win键+R键>"services.msc"
step2: "MYSQL">"启动"
#### 方式2
CMD> tasklist | findstr mysql
CMD> net start mysql # start 开启 stop 停止

退出mysql

EXIT;

版本

查看mysql数据库版本

【方法1】需要进入mysql
mysql> select version();

【方法2】Shell命令窗口下/不需要进入mysql
cmd> mysql --version 等效于 mysql -V

【方法3】需要进入mysql（查看数据库完整配置） 
mysql> status;  

【方法4】需要进入mysql
mysql --help |grep Distrib

卸载

OS: CENTOS6/7

卸载/安装前检查 MySQL的安装情况

rpm -qa | grep -i mysql

rpm -qa | grep -i mariadb

rpm -qa | grep -i Percona

卸载

rpm -e --nodeps xxxx(rpm包名)
      例如: 
            rpm -e --nodeps Percona-Server-shared-compat-57-5.7.24-27.1.el7.x86_64
            rpm -e --nodeps Percona-Server-shared-57-5.7.24-27.1.el7.x86_64
            rpm -e --nodeps Percona-Server-server-57-5.7.24-27.1.el7.x86_64
            rpm -e --nodeps Percona-Server-client-57-5.7.24-27.1.el7.x86_64

2 用户管理篇(含权限)

注意：任何涉及【用户管理与授权】的操作中↓↓↓

username@hostName/ip中的【hostName/ip】必须与【mysql.user表中的host字段一致，一旦不一致，任何关于【用户授权】的操作都将可能产生错误！】
查看mysql.user表各字段信息: USE mysql; SELECT host,user FROM mysql.user;
新版本 MySQL 8.0将【创建用户】和【授权】分开了
- 若【创建用户】与【授权】在同一语句执行(Eg： grant all privileges on test.* to root@'%' identified '123456'😉，则
  - 报错：ERROR 1410 (42000): You are not allowed to create a user with GRANT

MySQL8.0起 - 用户授权方法（先创建用户，再授权）
- USE mysql; ## 使用mysql数据库
- UPDATE user SET host='%' WHERE user='userName'; # 修改指定用户的host范围
- FLUSH PRIVILEGES; # 刷新权限表
- GRANT ALL PRIVILEGES ON dataBaseName.* to userName@host; # 授权
- FLUSH PRIVILEGES; # 刷新权限表

查看用户表

mysql> USE mysql;
mysql> SELECT host,authentication_string,user FROM user; # 命令1
mysql> SELECT host,user FROM mysql.user; # 命令2

更改某用户密码

# 方式1
ALTER USER userName@host IDENTIFIED BY 'newPassword'; # 尤其是： mysql 8.0之后的版本 初次修改root密码【亲测有效，MySQL8.0中发生重大改版】
FLUSH PRIVILEGES;

# 方式2
USE mysql; 
SET password FOR userName@host = password('newPassword');
FLUSH PRIVILEGES;

# SHOW GRANTS FOR 'johnny'@'%'; # 查看数据库中具体某个用户的权限

删除数据库用户

USE mysql;
DELETE FROM user WHERE user='jack' AND host='%';
FLUSH PRIVILEGES;

添加数据库用户

USE mysql; # 进入mysql系统数据库

# 方式A
CREATE USER userName@'localhost'; # 方式1
  -- CREATE USER userName@'%'; # 方式2 %: 任意主机
ALTER USER userName@host IDENTIFIED BY 'newPassword';
  -- SET PASSWORD FOR userName@'localhost'=PASSWORD('my-password');

-- 给用户 userName 授予数据库(db001)的 SELECT,INSERT,UPDATE,DELETE,CREATE,DROP 权限
-- GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON db001.* TO userName@'%' IDENTIFIED BY 'my-password'; -- mysql 5.x
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON db001.* TO userName@'%' WITH GRANT OPTION; -- mysql 8.x

CREATE user userName@'%' IDENTIFIED BY 'password'; # 方式3
ALTER USER userName@'%' IDENTIFIED BY 'password'; # 方式4

# 方式B
INSERT INTO user(
            host, user, password, 
            select_priv, insert_priv, update_priv) 
            VALUES ('localhost', 'guest', 
            PASSWORD('guest123'), 'Y', 'Y', 'Y');
            # 密码：guest123 授权：SELECT,INSERT 和 UPDATE
            
            # 所有权限：Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Reload_priv,Shutdown_priv,Process_priv,File_priv,Grant_priv,References_priv,Index_priv,Alter_priv,...
FLUSH PRIVILEGES;# 更新mysql数据库系统的用户权限列表

# 数据库用户授权方式2：
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON databaseName.* TO 'userName'@'%' IDENTIFIED BY 'passwordValue'; # %：任何【主机(ip)】都可访问到

# 数据库用户授权方式3：
    GRANT ALL PRIVILEGES ON databaseName.* TO userName; # 第1个*：其内指定数据库的【所有表】 
    GRANT ALL PRIVILEGES ON databaseName.* TO userName@'%';

# 回收权限
REVOKE DROP,UPDATE ON databaseName.* from userName;

# 查看指定用户的权限
SHOW GRANTS FOR 'userName'@'%';

查看指定用户的权限

SHOW GRANTS FOR 'userName'@'%';

查看系统所有权限

SHOW PRIVILEGES;# 授权见用户篇

授权指定表的部分权限给指定用户

grant select,create on databaseB.can_parse_matrix_signal_field_mapping to userA@'%' IDENTIFIED BY 'password of userA';
-- grant all privileges on *.* to userA@'password of userA'; 

-- GRANT SELECT, INSERT, UPDATE, DELETE ON master_xxx.xxx_logconfig TO 'username'@'%';

flush privileges; -- 刷新权限

如果数据库用户无对某表的访问权限时，会报如下类似错误：

Caused by: java.sql.SQLSyntaxErrorException: SELECT command denied to user 'xxx'@'localhost'

查看当前登录用户

SELECT user();
# 'johnny@localhost'


# 查看登录用户
select user(), current_user(); 
-- 正常响应本应：
| johnny@172.33.16.120 | johnny@% |

查看当前用户详细信息(权限等)

DESC USER;

查看数据库系统所有用户详细信息

SELECT * FROM USER;

SELECT User,Host,plugin,authentication_string,password_expired,password_last_changed FROM USER;

更改根用户密码

# 方式1
SET PASSWORD = PASSWORD('123456');
FLUSH PRIVILEGES;# 更新mysql数据库系统的用户权限列表

# 方式2
UPDATE mysql.user SET PASSWORD=PASSWORD('123456') WHERE user='root' and host='localhost';
FLUSH PRIVILEGES;# 更新mysql数据库系统的用户权限列表

给指定用户开放远程可访问指定数据库实例的权限

CASE1 给root用户开放远程访问权限

use mysql;

SELECT host,user FROM mysql.user; 
  # 确认root用户是否真的没有开放远程访问权限

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456';
  # 格式: GRANT ALL ON <database>.* TO '<user>'@'%' IDENTIFIED BY '<password>';

flush privileges; 
  # 因为MySQL把权限都放在了cache中；所以，在做完权限更改后需重新加载

3 数据库篇

创建数据库

DROP DATABASE ID EXISTS databaseName;
CREATE DATABASE databaseName;

CREATE DATABASE bdp_data_service DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `ga_jrsjy` /*!40100 DEFAULT CHARACTER SET utf8 */;

/*!32312 、/*!40100是指: 注解里!后紧跟版本号表示只有当前mysql版本高于这个版本号(3.23.12、4.01.00)时，才执行注解里的命令
IF NOT EXISTS：只有在该数据库(Eg：ga_jrsjy)不存在时，才创建该库。即若该库已经存在，则：不会再重新创建

使用(切换)指定数据库

USE databaseName;

删除指定数据库

# 假设已经登录数据库时
DROP DATABASE databaseName;

# 假设未登录数据库时
mysqladmin -u root -p DROP databaseName;

修改数据库的字符集

ALTER DATABASE tableName character SET utf8;#注意：字符集不能加字符'-'

查询所有数据库名

SHOW DATABASES; # 列出数据库中所有的数据库名

查看当前数据库

SELECT database();

查看数据库连接

方法1

这个命令会列出当前所有的连接，包括每个连接的ID、用户、主机、数据库、命令类型、运行时长等信息。

SHOW PROCESSLIST;

方法2

如果您想要看到更详细的线程信息，可以使用:

SHOW FULL PROCESSLIST;

这将显示完整的查询语句，这在调试和优化性能时非常有用。

Id	User	Host	Command	Time	State	Info	Memory_Used	Memory_used_by_user
46402836	cdc	172.xx.222.xxx:48722	Binlog Dump GTID	4665167	Master has sent all binlog to slave; waiting for more updates		14038	8208
46402839	cdc	172.xx.222.xxx:48728	Binlog Dump GTID	4665168	Master has sent all binlog to slave; waiting for more updates		13798	8208
52530737	biz_test	10.xx.37.xxx:64512	Query	0	starting/executing	/* ApplicationName=DBeaver 23.0.4 - SQLEditor <Script-304.sql> */ SHOW FULL PROCESSLIST	128018	9144

方法3

如果您想要通过SQL查询来获取当前活跃连接的数量，可以使用：

SELECT * FROM information_schema.processlist WHERE command != 'Sleep';

这个查询会排除掉所有处于空闲状态的连接（command为Sleep）

Id	User	Host	Command	Time	State	Info
46402391	cdc	172.xx.240.yy:38262	Binlog Dump GTID	4665576	Master has sent all binlog to slave; waiting for more updates
46402839	cdc	172.xx.222.xxx:48728	Binlog Dump GTID	4665168	Master has sent all binlog to slave; waiting for more updates
52530737	biz_test	10.xx.37.xxx:64512	Query	0	starting/executing	/* ApplicationName=DBeaver 23.0.4 - SQLEditor <Script-304.sql> */ SHOW FULL PROCESSLIST

4 表管理篇

创建表

-- DROP TABLE IF EXISTS `test`.`tb_student`;
CREATE TABLE `test`.`tb_student` (
      -- id INT NOT NULL PRIMARY KEY COMMENT '数据库ID',
      id INT NOT NULL COMMENT '数据库ID',
      
      -- name VARCHAR(50) COLLATE utf8mb4 NOT NULL COMMENT '姓名', 
      name VARCHAR(50) NOT NULL COMMENT '姓名', 
      
      sex VARCHAR(4) DEFAULT 'U' COMMENT '性别', 
      
      birthdate TIMESTAMP COMMENT '出生年月',
      grade INT COMMENT '年级',
      
      create_time DATETIME DEFAULT now() COMMENT '创建时间',
      update_time DATETIME DEFAULT now() ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
      
      PRIMARY KEY (`id`)
      , CONSTRAINT sex CHECK( sex IN ('F', 'M', 'U') )
) ENGINE=InnoDB DEFAULT CHARSET=utf8  COMMENT='学生信息表';

-- ALTER table `test`.`tb_student` ADD CONSTRAINT sex CHECK( sex IN ('F', 'M', 'U') );

demo2 : test.tb_user

-- create database test;

drop TABLE `test`.`tb_user`;
CREATE TABLE `test`.`tb_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID（代理键，数据库内置ID，用于构建主键索引）',
  `uuid` varchar(64) NOT NULL COMMENT '唯一索引（业务键，对外暴露的业务主键）',

  `username` varchar(64) NOT NULL COMMENT '用户名，用于登录',
  `nickname` varchar(64) NOT NULL COMMENT '用户昵称',
  `password` varchar(128) DEFAULT NULL COMMENT '用户密码',

  `log_file_uuid` varchar(64) DEFAULT NULL COMMENT 'LOGO文件的文件中心uuid(NULL表示系统默认头像)',
  `logo_file_url` varchar(1023) DEFAULT NULL COMMENT 'LOGO文件的OSS存储原始URL(NULL表示系统默认头像)',
  `disable` int(2) DEFAULT NULL COMMENT '禁用状态:0-激活/未禁用，1-已禁用',

  `remarks` varchar(255) DEFAULT NULL COMMENT '描述',
  `delete_flag` bit(1) DEFAULT NULL COMMENT '逻辑删除(0-未删除,1-已删除)',
  `create_by` varchar(31) DEFAULT NULL COMMENT '创建人员',
  `create_time` datetime(3) NOT NULL COMMENT '创建时间',
  `update_by` varchar(31) DEFAULT NULL COMMENT '更新人员',
  `update_time` datetime(3) DEFAULT NULL COMMENT '更新时间',
  `version` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '入库时间(数据库内置时间戳)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_idx_uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';


INSERT INTO `test`.`tb_user` (uuid , username, nickname , password , log_file_uuid, logo_file_url, disable, remarks , delete_flag , create_by , create_time, update_by, update_time, version) VALUES 
('8213572d-f5df-4127-aadd-bf0083c26da1' , 'jack', 'Jack~', md5('Hello'), null, null, 0, '', 0, '000001', '2024-11-01 09:00:00.179000000', '000001', '2024-11-01 09:00:00.179000000', now() ),
('8213572d-f5df-4127-aadd-bf0083c26da2' , 'jane', 'Jane', md5('Word'), null, null, 0, '', 0, '000001', '2024-11-01 09:05:00.179000000', '000001', '2024-11-01 09:05:00.179000000', '2024-11-01 09:05:00' );

select * from `test`.`tb_user`;

查看数据库的所有表

SELECT
      TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, 
      VERSION, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH,
      DATA_FREE, AUTO_INCREMENT, CREATE_TIME, UPDATE_TIME, CHECK_TIME, TABLE_COLLATION, CHECKSUM
FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = 'Etl_Source_jz';

查看数据库所有表及表注释

SELECT
    TABLE_NAME,
    TABLE_COMMENT
FROM
    information_schema.`TABLES`
WHERE
    TABLE_SCHEMA = 'Etl_Source_jz';

查看数据库所有表字段、字段注释

SELECT 
    TABLE_SCHEMA as DATABASE_NAME
    , TABLE_NAME , COLUMN_NAME
    , DATA_TYPE, COLUMN_TYPE , COLUMN_COMMENT
    , COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE 1=1
	and TABLE_SCHEMA in ( 'xxx_db', 'yyy_db' , 'zzz_db')
-- and DATA_TYPE in ('timestamp', 'datetime')

显示指定数据库的所有表名

USE databaseName;

SHOW TABLES

显示表的结构信息

SHOW COLUMNS FROM tableName; 
DESC tableName; #第二种方式

查询当前数据库所有表的详细信息

SHOW TABLE STATUS  FROM tableName; # 查询当前数据库所有表的详细信息

SHOW TABLE STATUS LIKE 'xxx%'; # 表名以xxx开头的表的信息

SHOW TABLE STATUS FROM tableName LIKE 'xxxx%'\G;   # 加上 \G，查询结果按列打印

查看表引擎

查看数据库支持的表引擎

SHOW ENGINES

查看指定数据库所使用的表引擎

方法1

> use {dbName};
> SHOW TABLE STATUS LIKE '{tableName}';

# output: (随意取1行为例)
`Name` tb_config
`Engine` InnoDB
`Version` 10
`Row_format` Dynamic
`Rows` 5
`Avg_row_length` 3276
`Data_length` 16384
`Max_data_length` 0
`Index_length` 16384
`Data_free` 0
`Auto_increment` 6
`Create_time` 2023-12-12 14:06:59
`Update_time` 2023-12-12 14:07:19
`Check_time` 
`Collation` utf8mb4_general_ci
`Checksum` 
`Create_options` 
`Comment` XXXX配置表

方法2:

SELECT 
	TABLE_SCHEMA, TABLE_NAME, ENGINE 
FROM information_schema.TABLES 
WHERE 
	TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
	and TABLE_NAME like 'vhr_canconfig'

查看当前数据库中所有表的行数

use information_schema;

select table_name,table_rows from tables where TABLE_SCHEMA = '<数据库名称>' order by table_rows desc;

添加表的列

ALTER TABLE table_name ADD COLUMN columnB VARCHAR(10);

ALTER TABLE dbName.tb_user_account ADD user_logo_url varchar(100) NULL COMMENT '用户头像地址' AFTER phone_number;

ALTER TABLE xxx_datasource.t_datasource_info ADD datasource_category varchar(100) COMMENT '数据源分类（一级分类）' AFTER description; // AFTER / FIRST

-- [亲测] MYSQL 5.7.37 / 8.0.39 等均不支持在增/删列时判断列的存在性(IF NOT EXISTS)的语法
-- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT exists email VARCHAR(255) NOT NULL' at line 1
ALTER TABLE `test`.`t_user` ADD COLUMN IF NOT EXISTS `email` VARCHAR(255) NOT NULL;

用存储过程解决此需求，参见:

CASE 当列不存在时方新增列 - 博客园/千千寰宇

删除表的某列

ALTER TABLE table_name DROP COLUMN field_name;

修改表的表名

ALTER TABLE table_nameA RENAME table_nameB;

修改表的列名

ALTER TABLE table_name CHANGE COLUMN columnA columnB VARCHAR(30) COMMENT 'commentValue';

修改表的列

ALTER TABLE [dbName.]tableName MODIFY id BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT; -- 修改列(数据类型、nullable、primary key、increment)

ALTER TABLE table_name MODIFY columnA CHAR(10) -- 修改 表列类型  

ALTER TABLE table-name ADD 
    PRIMARY KEY ( table-id ); -- 添加 主键
ALTER TABLE table-name ADD  -- 添加 外键
    CONSTRAINT foreign-key-name 
    FOREIGN KEY(curent-table-column-name) REFERENCES pk-table-name(pk-table-column-name)

ALTER TABLE table-name DROP PRIMARY KEY; -- 删除 主键
ALTER TABLE table-name DROP FOREIGN KEY foreign-key-name; -- 删除 外键

修改某列的值为X或者为空

UPDATE table_name SET field_name = 'X';
UPDATE table_name SET field_name = '';

强制禁用所有表的外键约束

用于删除表时使用

GLOBAL FOREIGN_KEY_CHECKS 基于全局
FOREIGN_KEY_CHECKS 基于临时的Session

SET FOREIGN_KEY_CHECKS = 0; # 0:禁用外键约束 1:启用外键约束 (FOREIGN_KEY_CHECKS基于session，当关闭session重新建立连接，本变量会自动恢复默认值)

5 索引管理篇

5.1 索引简述

unique/primary key/index 的意义

当新增/修改的 unique key 的column值出现重复时，数据库将报"Duplicate entry '{columnValue}' for key {keyName}"错误

5.2 创建索引

方式1

(亲测有效 on mysql 5.7.38-220701-log at 202212281919)

#format: ALTER TABLE {tableName} ADD [UNIQUE] INDEX|KEY {indexName} (columnA [(length)] [ASC|DESC]);

#sample: ALTER TABLE service_base_info ADD UNIQUE INDEX index_unique_for_service_id_on_service_base_info (service_id);

方式2

(亲测无效 on mysql 5.7.38-220701-log at 202212281919)

#format: create unique index {indexName} on {tableName}(columnA[,columnB,...])

#sample: create index index_unique_for_service_id_on_service_base_info on service_base_info(service_id);

5.3 查看索引

显示数据表的详细索引(含主键)

#format: SHOW INDEX FROM <表名> [ FROM <数据库名>];

#sample: show index from service_base_info from bdp_data_service;

5.4 删除索引

语法格式

ALTER  TABLE  table_name   DROP  INDEX  index_name;

代码示例

mysql> ALTER  TABLE  score  drop  INDEX  id;
Query OK, 0 rows affected (0.65 sec)

6 字符集与排序规则篇

在建库时，一般选择字符集为utf8mb4，排序规则为utf8mb4_general_ci的方式

简述

MySQL中使用命令SHOW CREATE TABLE t可以查看建表语句，如下所示，我们可以看到描述中有两个关键字CHARSET和COLLATE，其中CHARSET可能很多人都大概知道，但是对COLLATE了解的人可能就比较少了。这篇文章就简单介绍一下MySQL中的CHARSET和COLLATE这两个关键字。

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

字符集/编码集(CHARSET)

查看系统默认字符集

CHARSET表示数据编码方式，在MySQL中可以使用命令show variables like 'character_set_%';查看系统默认编码集。

> show variables like 'character_set_%';
character_set_client	utf8mb4
character_set_connection	utf8mb4
character_set_database	utf8
character_set_filesystem	binary
character_set_results	
character_set_server	utf8
character_set_system	utf8
character_sets_dir	/usr/local/mysql-5.7.38-221001-linux-x86_64/share/charsets/

> show variables like 'character_set_%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | latin1                         |
| character_set_connection | latin1                         |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | latin1                         |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8                           |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)

utf8 与 utf8mb4 的选择

在我们的业务场景中因为经常会涉及到中文，常用的编码方式就是utf-8，但是在MySQL中的utf8编码其实并不是我们平时使用的utf-8编码，正确的对应编码是utf8mb4，在MySQL中utf8编码最多只能支持3 bytes长度的字符编码，对于一些需要占用4 bytes的字符使用utf8编码方式就不支持了（此时会出现编码、解码失败==>乱码），需要使用utf8mb4才行，这是MySQL的一个遗留问题。
我们在使用MySQL时，如果使用的是MySQL 5.7及以前的版本，需要我们手动配置编码方式为utf8mb4；如果使用的是版本 MySQL 8+则默认就是该编码了，不需要我们再修改了。修改MySQL默认字符集，可以在/etc/my.cnf配置文件中修改对应的编码即可，注意不同的版本修改方式可能有些不太一样。

排序规则(COLLATE)

简述

COLLATE属性用于指定列的排序和比较方式，我们在使用ORDER BY、DISTINCT、GROUP BY等命令时都会涉及到该属性。另外，我们在对表建索引时，如果索引列是字符类型，那么COLLATE属性也会影响到索引的创建。
COLLATE通常是和字符编码CHARSET相关的，每种CHARSET都对应了多种它所支持的COLLATE，并且会指定一个默认COLLATE。比如utf8mb4编码的默认COLLATE为utf8mb4_general_ci，对于中文场景来说，其实utf8mb4_unicode_ci和utf8mb4_general_ci没有什么区别。

大小写不敏感(`_ci`)与大小写敏感(`_cs`)

我们会发现，很多COLLATE里面都带有_ci后缀，这是Case Insensitive的缩写，表示大小写无关。对应的_cs后缀表示大小写敏感。

需要注意的是，在MySQL 8+版本中，默认的CHARSET直接改成了utf8mb4，对应的默认COLLATE也改成了utf8mb4_0900_ai_ci，这是unicode的一个细分，就相当于utf8mb4_unicode_ci。

查看支持的所有排序规则

我们可以使用命令show collation;查看MySQL所支持的COLLATE。

utf8_general_ci 不区分大小写，这个你在注册用户名和邮箱的时候就要使用。
utf8_general_cs 区分大小写，如果用户名和邮箱用这个就会照成不良后果
utf8_bin 字符串每个字符串用二进制数据编译存储。区分大小写，而且可以存二进制的内容
utf8_unicode_ci，和`utf8_general_ci对中、英文来说没有实质的差别。
utf8_general_ci 校对速度快，但准确度稍差。（准确度够用，一般建库选择这个）
utf8_unicode_ci 准确度高，但校对速度稍慢。
utf8mb4_bin
utf8mb4_unicode_ci 【推荐】
utf8mb4_0900_ai_ci 【推荐】
utf8mb4_general_ci 【推荐】

MySQL设置编码集/排序规则的其他指令

MySQL设置编码命令

SET character_set_client = utf8;
SET character_set_connection = utf8;
SET character_set_database = utf8;
SET character_set_results = utf8;
SET character_set_server = utf8;

SET collation_connection = utf8_bin;
SET collation_database = utf8_bin;
SET collation_server = utf8_bin;


# my.ini中配置默认编码
default-character-set=utf8


# 连接数据库设置编码
jdbc:mysql://192.168.18.1:3306/test?characterEncoding=utf8


# java中的常用编码: UTF-8; GBK; GB2312; ISO-8859-1;
# 对应mysql数据库中的编码: utf8/utfmb4; gbk; gb2312; latin1

建库时指定库的字符集与排序规则

CREATE DATABASE bdp_data_service DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

建表时指定表/字段的字符集与排序规则

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TABLE IF EXISTS `alipay_config`;
CREATE TABLE `alipay_config`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `app_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '应用ID',
  `charset` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '编码',
  `format` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '类型 固定格式json',
  `gateway_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '网关地址',
  `notify_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '异步回调',
  `private_key` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '私钥',
  `public_key` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '公钥',
  `return_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '回调地址',
  `sign_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '签名方式',
  `sys_service_provider_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '商户号',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

7 数据类型

text

样例：xxljob的xxl_job_log表 trigger(text)字段用来存储调度结果的HTML日志内容。

-- xxl_job.xxl_job_log definition

CREATE TABLE `xxl_job_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `job_group` int(11) NOT NULL COMMENT '执行器主键ID',
  `job_id` int(11) NOT NULL COMMENT '任务，主键ID',
  `executor_address` varchar(255) DEFAULT NULL COMMENT '执行器地址，本次执行的地址',
  `executor_handler` varchar(255) DEFAULT NULL COMMENT '执行器任务handler',
  `executor_param` varchar(512) DEFAULT NULL COMMENT '执行器任务参数',
  `executor_sharding_param` varchar(20) DEFAULT NULL COMMENT '执行器任务分片参数，格式如 1/2',
  `executor_fail_retry_count` int(11) NOT NULL DEFAULT '0' COMMENT '失败重试次数',
  `trigger_time` datetime DEFAULT NULL COMMENT '调度-时间',
  `trigger_code` int(11) NOT NULL COMMENT '调度-结果',
  `trigger_msg` text COMMENT '调度-日志',
  `handle_time` datetime DEFAULT NULL COMMENT '执行-时间',
  `handle_code` int(11) NOT NULL COMMENT '执行-状态',
  `handle_msg` text COMMENT '执行-日志',
  `alarm_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '告警状态：0-默认、1-无需告警、2-告警成功、3-告警失败',
  PRIMARY KEY (`id`),
  KEY `I_trigger_time` (`trigger_time`),
  KEY `I_handle_code` (`handle_code`)
) ENGINE=InnoDB AUTO_INCREMENT=9141909 DEFAULT CHARSET=utf8mb4;

mediumtext

数据类型的定义

在MySQL中，mediumtext是一种文本数据类型，用于存储中等长度的文本字符串。它可以存储最大长度为16,777,215个字符（或者3个字节的UTF-8字符）。mediumtext比text类型可以存储更多的字符。

可以使用mediumtext来存储较长的文本，如博客文章、新闻稿、论文等。它可以存储大量文本数据，但不适合存储超过16,777,215个字符的非结构化文本。

数据类型的特点

以下是mediumtext类型的一些特点：

最大长度为16,777,215个字符。

占用3个字节的UTF-8字符。

可以存储较长的文本数据，但不适合存储超过16,777,215个字符的非结构化文本。

可以用来存储大型文本，如博客文章、新闻稿、论文等。

样例: xxljob的xxl_job_logglue表 glue_source(mediumtext)字段用来存储 shell 等脚本内容。

-- xxl_job.xxl_job_logglue definition

CREATE TABLE `xxl_job_logglue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `job_id` int(11) NOT NULL COMMENT '任务，主键ID',
  `glue_type` varchar(50) DEFAULT NULL COMMENT 'GLUE类型',
  `glue_source` mediumtext COMMENT 'GLUE源代码',
  `glue_remark` varchar(128) NOT NULL COMMENT 'GLUE备注',
  `add_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=162 DEFAULT CHARSET=utf8mb4;

8 数据库SQL常用語句

刪除

DELETE FROM table_name [WHERE Clause]

查看

SELECT column_name,column_name
    FROM table_name
    [WHERE Clause]
    [LIMIT N][ OFFSET M]

修改

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

注：易错一条UPDATE的SQL同时修改多个字段值，字段与字段之间使用逗号,，而非and！
否则，MySQL数据库会报此类异常:Data truncation: Truncated incorrect DOUBLE value:??
推荐本文: [数据库/MySQL]解决异常：Data truncation: Truncated incorrect DOUBLE value: 'dc5' - 千千寰宇

新增/插入

INSERT INTO table_name ( field1, field2,...fieldN ) 
  VALUES ( value1, value2,...valueN );

INSERT INTO target_db.target_table( field1, field2, ... ) 
  SELECT value1,value2, ... FROM oringin_db.origin_table

注释

单行注释: # --

# SELECT * FROM test.department;

-- SELECT * FROM test.department;

多行注释: /* sql content */

/*
SELECT * FROM test.department;
*/

Y FAQ

Q1 客户端连接`Mysql 8`报错：`Public Key Retrieval is not allowed`

解决思路

在我们使用MySQL8.0的，连接数据库会存在一定的问题
当提示：Public Key Retrieval is not allowed 错误的时候，我们可以在连接数据库的配置文件中加上:

allowPublicKeyRetrieval=true

完整配置如下

url: jdbc:mysql://localhost:3306/ry?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
username: root
password: root

参考文献

Mysql 8报错：Public Key Retrieval is not allowed（解决方式） - CSDN

X 推荐文献

posted @ 2017-11-29 21:18 千千寰宇阅读(652) 评论(0) 编辑收藏举报

刷新页面返回顶部

千千寰宇

大数据与Java软件开发从业者，数智化转型实践者。