MYSQL

mysql官方全部文档

java类型对应

mysql官方类型对应文档

配置

# mysql客户端设置
# 为所有的连接客户端工具，包括mysql命令行和msqldump等等
# 如果有[mysql]组的设置，则mysql命令行连接的优先使用[mysql]组的配置
[client]
default-character-set = utf8mb4

# mysql命令行设置
[mysql]
default-character-set = utf8mb4

# mysql服务端设置（mysqld表示mysqld进程）
[mysqld]
################################################################################################
# 数据库引擎与字符集相关设置

# mysql 5.1 之后，默认引擎就是InnoDB了
default_storage_engine = InnoDB

# 内存临时表默认引擎，默认InnoDB
default_tmp_storage_engine = InnoDB

# mysql 5.7新增特性，磁盘临时表默认引擎，默认InnoDB
internal_tmp_disk_storage_engine = InnoDB

# 数据库默认字符集,主流字符集支持一些特殊表情符号（特殊表情emoji符占用4个字节）
character-set-server = utf8mb4

# 数据库字符集对应一些排序等规则，注意要和character-set-server对应
collation-server = utf8mb4_unicode_ci

# 设置client连接mysql时的字符集,防止乱码
init-connect = 'set names utf8mb4'

# ps：查看数据库字符集与字符序：
# show variables where variable_name like '%character%' or variable_name  like '%collation%';
# 1. character_set_client：客户端来源数据使用的字符集
# 3. character_set_connection：连接层字符集
# 2. character_set_server：默认的内部操作字符集
# 4. character_set_results：查询结果字符集
# 5. character_set_database：当前选中数据库的默认字符集
# 6. character_set_system：系统元数据(字段名等)字符集

# MySQL中的字符集转换过程
# 1. MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection；
# 2. 进行内部操作前将请求数据从character_set_connection转换为内部操作字符集，其确定方法如下：
#     使用每个数据字段的CHARACTER SET设定值；
#     若上述值不存在，则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展，非SQL标准)；
#     若上述值不存在，则使用对应数据库的DEFAULT CHARACTER SET设定值；
#     若上述值不存在，则使用character_set_server设定值。
# 3. 将操作结果从内部操作字符集转换为character_set_results。

################################################################################################
# 数据库时区设置

# 默认time-zone=system,即系统的时区。ps：设为0时区是为了统一时间
# ps：查看数据库时区：show variables like "%time_zone%";
default-time-zone = '+00:00'

索引

索引增加删除与查看

-- 添加索引
ALTER TABLE table_name ADD INDEX index_name (column_list)
-- 查看索引
SHOW INDEX FROM table_name;
-- 删除索引
ALTER TABLE table_name DROP INDEX index_name

索引创建注意事项

• 对选择性高的列创建索引，反例：性别字段

• 对于比较长的字段，需要使用前缀索引，前缀也需要选择性足够高，并且注意一次查询的 返回结果比例最好不大于30%

    SELECT  COUNT(DISTINCT city)/COUNT(*) AS sel,
            COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3,
            COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4,
            COUNT(DISTINCT LEFT(city, 5))/COUNT(*) AS sel5,
         	COUNT(DISTINCT LEFT(city, 6))/COUNT(*) AS sel6,
         	COUNT(DISTINCT LEFT(city, 7))/COUNT(*) AS sel7
    FROM city_demo;
  

  sel	sel3	sel4	sel5	sel6	sel7
  0.0312	0.0239	0.0293	0.0305	0.0309	0.0310

  例如上表，sel7前缀索引的选择性接近于索引整个列sel，所以可以选前缀为7个字符

• 最好针对整个mysql数据库的慢查询语句与查询语句的使用频繁度来进行索引创建，而不是单独某个语句

索引强制使用/不使用

• 强制对某条语句使用/不使用索引,force/ignore index(索引名或者主键PRI)

    -- 强制使用主键
    SELECT * FROM city_demo FORCE INDEX(PRI) LIMIT 1;
    -- 强制使用索引 idx_city
    SELECT * FROM city_demo FORCE INDEX(idx_city) LIMIT 1;
  

索引失效情况

• 查询条件不满足最左前缀不会使用索引，例：

    SELECT * FROM city_demo WHERE city LIKE '%s%';
  

• 索引项包含表达式不会使用索引，例：

    SELECT * FROM city_demo WHERE concat(city,'s') = 'city';
  

• 使用or连接的条件，包含无索引的项则不会使用索引

索引优化

• 根据explain进行优化查询语句
• 使用覆盖索引避免回表，（使用explain，可以看到Extra字段为Using Index，代表从索引中查询）
• 多个相同项的or条件优化为 in 查询

• 对于limit的分页查询，扫描行数过大导致过慢，可以优化为延迟关联查询，例：

    SELECT <cols> FROM profiles WHERE sex = 'M' ORDER BY rating LIMIT 100000, 10;
  

  优化为

    SELECT <cols> FROM profiles INNER JOIN (
    SELECT <primary kry cols> FROM profiles
    WHERE x.sex = 'M' ORDER BY rating LIMIT 100000, 10
    ) AS x USING(<primary key cols>);
  

常用命令

语法

筛选条件先后顺序where , group by , having , order by , limit

-- 同个字段的2种条件
SELECT id,score,IF (score >= 60, '及格', '不及格') AS 语文 FROM score_test;
-- 同个字段的多种条件
SELECT id,score,(CASE WHEN score >= 80 THEN '优秀' WHEN score >= 60 THEN '及格' ELSE '不及格' END) AS 语文 FROM score_test;

查看帮助

mysql --help

连接mysql

mysql -uroot -p123123
## 直接进入数据库 sun8min_user
mysql -uroot -p123123 sun8min_user

查看所有database

SHOW DATABASES;

进入database

USE sun8min_user;

查看所有table

SHOW TABLES;

查看表结构

DESC city_demo;

将表的行按列输出显示\G

SELECT * FROM city_demo LIMIT 2\G

其他命令

字段增删改

-- 字段增加
ALTER TABLE city_demo ADD i INT;
-- 字段修改
ALTER TABLE city_demo MODIFY i CHAR(10);
-- 字段删除
ALTER TABLE city_demo DROP i;

密码修改

ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';

查看编码

SHOW VARIABLES LIKE 'character%';

查看时区

SHOW VARIABLES LIKE "%time_zone%";

数据导出

mysqldump -u<username> -p<password> [OPTIONS] <database name> [table name] > db.sql

例:

-- 导出sun8min_admin库
mysqldump -uroot -p123123 sun8min_admin > db.sql
-- 导出sun8min_admin库的sowing表结构
mysqldump -uroot -p123123 -d sun8min_admin sowing  > db.sql

-- 相关解释
-d: no-data，不要导出数据

数据导入

• 进入数据库执行

    -- 设置编码
    set names utf8;
    -- 导入数据库
    source /var/lib/mysql/db.sql;
  

• 外部执行

    ## 导入mysql
    mysql -uroot -p123123 < /var/lib/mysql/db.sql
    ## 导入指定数据库sun8min_admin
    mysql -uroot -p123123 sun8min_admin < /var/lib/mysql/db.sql
  

数据库脚本示例

-- 用户库
drop database if exists sun8min_user;
create database sun8min_user default charset utf8mb4 collate utf8mb4_unicode_ci;
use sun8min_user;
-- -- 用户表
drop table if exists sun8min_user;
create table sun8min_user
(
  user_id         bigint unsigned auto_increment comment '用户id',
  user_nick_name  varchar(16)         not null comment '用户显示名',
  user_real_name  varchar(16)         not null default '' comment '用户真实名',
  user_phone      varchar(32)         not null default '' comment '用户手机号',
  user_sex        tinyint             not null default 0 comment '用户性别（0:未知，1:男，2:女）',
  user_password   varchar(140)         not null comment '用户密码',
  extension_field varchar(255)        not null default '' comment '扩展字段（json格式）',
  version         int unsigned        not null default 0 comment '版本号（用于乐观锁）',
  gmt_create      datetime            not null default current_timestamp comment '创建时间',
  gmt_modified    datetime            not null default current_timestamp on update current_timestamp comment '修改时间',
  is_deleted      tinyint(1) unsigned not null default 0 comment '是否删除（0：否，1：是）',
  primary key (user_id),
  unique key uk_user_nick_name(user_nick_name)
) engine = innodb
  default charset = utf8mb4
  collate = utf8mb4_unicode_ci
  auto_increment = 1 comment '用户表';

-- ###################################
-- 基础数据插入

-- 用户库
use sun8min_user;
-- -- 用户表
insert into sun8min_user(user_nick_name, user_password)
values ('aaa', '$2a$10$MN0ixnUyRwVoX3PsBw4cuOZoxkoyIqnS0vx705Debz27SRXJkhXJ.'),
       ('bbb', '$2a$10$ZGg3HuuVbdSKzjklW2XsxuWdmDFR0WB7MjjxaIwNWc.tkLEjJdn1m'),
       ('ccc', '$2a$10$d19vFkjCz.ChXbOHDFrsIe/nkfARjwOvvBf1d0ILmqSpzeqCz1P5e');

常见问题

时区差

客户端：时间 -> 根据客户端与服务端当前时间差 -> 服务端：时间

一般时间都存为datetime类型，在mysql中为’YYYY-MM-DD HH:MM:SS’的字符串，不带时区，时区在配置文件中配置， 所以如果mysql服务端时区改变，客户端继续存时间，则会造成同一张表里头的时间的时区不一致的情况。

emoji表情保存失败

emoji表情需要使用utf8mb4存储，所以数据库字符集应该设置为utf8mb4，再根据需要设置不同表的字符集，然后重启mysql， 详见：mysql配置文件。在使用驱动连接mysql时，对于8.0.12及以下版本: characterEncoding=utf8 表示utf8，对于8.0.13及以上版本表示utf8mb4。

附：mysql官方字符集文档

• Previous
  Git
• Next
  Tool WebSite