实践MySQL

Posted on | In | Visitors

安装

Debian

deepin 15.11 64 bit

参考官方Installing MySQL on Linux Using the MySQL APT Repository

先下载安装MySQL APT Repository

1
2
# 会弹出MySQL Apt Config配置界面
$ sudo dpkg -i mysql-apt-config_0.8.15-1_all.deb

安装

1
$ sudo apt install mysql-server

Redhat

1
2
3
4
5
6
7
8
9
10
# root下执行
$ wget https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rp
$ rpm -ivh mysql80-community-release-el7-3.noarch.rpm
$ yum repolist all | grep mysql
# 选择版本
$ yum-config-manager --disable mysql80-community
$ yum-config-manager --enable mysql56-community
$ yum list mysql-community-server
# 初始化，比如密码等
$ mysql_secure_installation

服务管理

1
2
3
4
5
6
7
8
$ systemctl disable mysql
Removed symlink /etc/systemd/system/multi-user.target.wants/mysqld.service.
Removed symlink /etc/systemd/system/mysql.service.

# mysql和mysqld要注意
$ systemctl enable mysqld
Created symlink from /etc/systemd/system/mysql.service to /usr/lib/systemd/system/mysqld.service.
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.

重置root密码

deepin15.7中安装mysql7，发现没有设置过root密码，用如下方法重置：

1、修改/etc/mysql/mysql.conf.d/mysqld.cnf，在最后增加skip-grant-tables，然后重启mysql。

2、不用秘密直接登录（直接回车），mysql -u root -p

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mysql';
Query OK, 0 rows affected (0.00 sec)

3、删除步骤1中新增的skip-grant-tables，重启mysql即可。

数据库

实体分层

mysql 的实体分层由上至下依次是：

  • 实例（instance）

    对应mysqld 进程

  • 库（database）

  • 表（table）

  • 记录（row）

  • 字段（field）

从应用层面看，MySQL的数据库是用schema定义的。

1
2
3
4
5
6
7
8
9
-- 展示数据库列表
show databases;
-- 删除
drop database if exists abc;
-- 新建
CREATE database if NOT EXISTS abc default character set utf8mb4 collate utf8mb4_general_ci;
CREATE database if NOT EXISTS abc default character set utf8 collate utf8_general_ci;
-- 选择数据库
use abc;

连接

1
2
3
4
5
$ mysql -u<user> -p
$ mysql -u<user> -p<password>
$ mysql -u<user> -h<host> -p<password>
$ mysql -u<user> -h<host> -P<port> -p<password>
$ mysql -u<user> -h<host> -P<port> -D<databaseName> -p<password>

连接数

查看

1
2
3
4
5
6
7
mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

修改

1
2
[mysqld]
max_connections = 1024

参考：mysql连接数过多 解决方案

执行进程

查询进程

mysql> show processlist;
Id     User            Host                  db              Command  Time   State                            Info                                                                                                  
-----  --------------  --------------------  --------------  -------  -----  -------------------------------  ----------------------------------------------------------------------------------------------------                                                                               
57631  report_zh       192.168.1.201:53468   report_zh       Sleep    20                                      (null)                                                                                                
57632  zhkj_mall       192.168.1.201:53470   zhkj_mall       Sleep    20                                      (null)                                                                                                
57832  report_zh       192.168.1.53:45718    report_zh       Query    64292  Sending data 
        select count(1) from cus_customer

结束进程

mysql> kill 57631;

字符集

1
mysql> show charset;

查询字符集：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
mysql> show variables like '%character%';
-- utf8
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

-- utf8mb4
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.05 sec)


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> show variables like '%collation%';
-- utf8_general_ci
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

-- utf8mb4_general_ci
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8_general_ci    |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

设置字符集，修改配置文件，比如：/etc/mysql/my.cnf

[mysqld]
character-set-server=utf8mb4

修改数据库字符集

1
2
mysql> ALTER DATABASE ums DEFAULT CHARSET=utf8;
mysql> ALTER DATABASE ums DEFAULT COLLATE=utf8_general_ci;

修改表字符集

1
2
mysql> ALTER TABLE `ad_advertise` DEFAULT CHARSET=utf8mb4;
mysql> ALTER TABLE `ad_advertise` DEFAULT COLLATE=utf8mb4_general_ci

时区

mysql> show variables like "%time_zone%";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

time_zone说明mysql使用system的时区，system_time_zone说明system使用CST时区。

方法一

实时生效，重启失效

mysql> set global time_zone = '+8:00';  ##修改mysql全局时区为北京时间，即我们所在的东8区
mysql> set time_zone = '+8:00';  ##修改当前会话时区
mysql> flush privileges;  #立即生效

方法二

[mysqld]
default-time_zone = '+8:00'

jdbc连接串指定时区serverTimezone=GMT%2B8

大小写

lower_case_table_names参数用于控制存储大小写和是否区分大小写，修改需要重启数据库生效。

0：表名存储为指定的大小写（按创建时是大写还是小写定），但是使用时（比如：select）区分大小写，默认值；

1：表名存储时全部转换为小写，但是比较的时候是不区分大小写；

2：表名存储为指定的大小写（按创建时是大写还是小写定），但是使用时（比如：select）用小写；

1
mysql> show variables like 'lower_case_table_names';

配置如下：

1
2
[mysqld]
lower_case_table_names=1

注释

1
2
3
4
5
6
7
8
#注释
select * from dfg;
-- 注释（前面有一个空格）
select * from abc;
/*
多行注释
*/
select * from cde;

引擎

查看数据库支持的引擎

1
mysql> show engines;

设置默认引擎，修改配置文件，比如：/etc/my.cnf

[mysqld]
default-storage-engine=INNODB

查询当前数据库的引擎

1
mysql> show variables like '%storage_engine%';

查询表的引擎

1
2
mysql> show create table table_name;
mysql> show table status from db_name where name='table_name';

修改表引擎

1
mysql> alter table table_name engine=innodb;

数据目录

[mysqld]
datadir=/var/lib/mysql

表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
drop table if exists sys_dept;
create table 
	sys_dept
    (
        dept_id int(11) not null auto_increment comment '部门id',
        parent_id int(11) default 0 comment '父部门id',
        ancestors varchar(50) default '' comment '祖级列表',
        dept_name varchar(30) default '' comment '部门名称',
        order_num int(4) default 0 comment '显示顺序',
        leader varchar(20) default '' comment '负责人',
        phone varchar(11) default '' comment '联系电话',
        email varchar(50) default '' comment '邮箱',
        status char(1) default '0' comment '部门状态（0正常 1停用）',
        del_flag char(1) default '0' comment '删除标志（0代表存在 2代表删除）',
        create_by varchar(64) default '' comment '创建者',
        create_time datetime comment '创建时间',
        update_by varchar(64) default '' comment '更新者',
        update_time datetime comment '更新时间',
        primary key (dept_id)
    )
    engine=innodb auto_increment=200 default charset=utf8 comment = '部门表';

1
2
3
4
ALTER TABLE abc ADD SYSTEM_CODE VARCHAR(32) comment '系统代码';
ALTER TABLE abc delete SYSTEM_CODE;
# 修改列备注
alter table cms_blog modify column content varchar(512) comment '内容';

MySQL要求一个行的定义长度不能超过65535。

（1）单个字段如果大于65535，则转换为TEXT 。

（2）单行最大限制为65535，这里不包括TEXT、BLOB。

超过长度报错如下：

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check your manual. You have to change some columns to TEXT or BLOBs

排序

当排序值相同时，查询结果不是幂等的，有可能同样的查询两次结果不一样，可以通过增加一个排序字段，确保排序字段唯一。

索引

隶属于表，表下名称唯一

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE INDEX
    idx_biz_log_id_system_code
ON
    `account`.`acc_account_inf`
    (
        `BIZ_LOG_ID` asc,
        `SYSTEM_CODE` desc
    );

CREATE UNIQUE INDEX
    idx_biz_log_id_system_code
ON
    `account`.`acc_account_inf`
    (
        `BIZ_LOG_ID`,
        `SYSTEM_CODE`
    );
    
CREATE fulltext INDEX idx_biz_log_id_system_code ON `account`.`acc_account_inf` (`BIZ_LOG_ID`,
`SYSTEM_CODE`);

drop index idx_biz_log_id_system_code on acc_account_inf;

用户

通过授权的方式创建用户（deprecated）

1
2
GRANT ALL PRIVILEGES ON abc.* TO 'abc'@'%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON abc.* TO 'abc'@'%' IDENTIFIED BY '123456' with grant option;

单独创建用户

1
create user 'user'@'host' identified by '123456'

查询用户

1
2
mysql > use mysql
mysql > select user,host from user;

grant

1
mysql > show grants for root@'localhost';

执行sql文件

方法一

1
$ mysql -h localhost -u root -p123456 < F:\test\test.sql

备注：执行后没有结果提示信息。

方法二

1
2
# 先登录到mysql
mysql>source F:\test\test.sql

方法三

1
2
# 先登录到mysql
mysql>\. F:\test\test.sql

1
WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

直接执行sql

1
$ mysql -h localhost -u root -p123456 -e 'select 1 from dual'

查询结果输出到文件

方法一

1
$ mysql -h localhost -u root -p123456 -e 'select 1 from dual' -p > abc.txt

方法二

mysql> pager cat > /tmp/abc.txt;
PAGER set to 'cat > /tmp/abc.txt'
mysql> select 1 from dual;
1 row in set (0.00 sec)

mysql>

分页

不用统计总条数和总页数，仅提供下一页和上一页功能

currentLastValue：代表当前页最后一条记录的a1值，首页时为空

currentFirstValue：代表当前页第一条记录的a1值，首页时为空

升序分页

首页

1
SELECT a1, b1 FROM a limit 20 order a1 asc;

下一页

1
SELECT a1, b1 FROM a where a1 > {currentLastValue} limit 20 order a1 asc;

上一页

1
SELECT a1, b1 FROM a where a1 < {currentFirstValue} limit 20 order a1 desc;

例如：

1
2
3
4
5
6
7
-- 首页（比如：1~20）
SELECT a1, b1 FROM a limit 20 order by a1 asc;
-- 下一页（比如：21~40）
SELECT a1, b1 FROM a where a2 > 20 limit 20 order by a1 asc;

-- 上一页（比如：20~1）
SELECT a1, b1 FROM a where a1 < 21 limit 20 order by a1 desc;

降序分页

首页

1
SELECT a1, b1 FROM a limit 20 order a1 desc;

下一页

1
SELECT a1, b1 FROM a where a1 < {currentLastValue} limit 20 order a1 desc;

上一页

1
SELECT a1, b1 FROM a where a1 > {currentFirstValue} limit 20 order a1 asc;

例如：

1
2
3
4
5
6
-- 首页（比如：40~21）
SELECT a1, b1 FROM a limit 20 order by a1 asc;
-- 下一页（比如：20~1）
SELECT a1, b1 FROM a where a2 < 21 limit 20 order by a1 asc;
-- 上一页（比如：21~40）
SELECT a1, b1 FROM a where a1 < 20 limit 20 order by a1 desc;

其他特性

ON DUPLICATE KEY UPDATE

1
2
3
4
insert into daily_hit_counter (day, slot, cnt) values ('2017-11-19', 2, 1) ON DUPLICATE KEY UPDATE cnt = cnt + 1;

-- 唯一性冲突则更新cnt
insert into daily_hit_counter (day, slot, cnt) values ('2017-11-19', 2, 1) ON DUPLICATE KEY UPDATE slot = values(slot), cnt = values(cnt);

binlog

开启binlog

1
2
3
log-bin=mysql-bin
server-id=1
binlog_format=MIXED

查看

show variables like 'log_bin%';

相关书籍

《高性能MySQL（第3版）》，MySQL 5.5

《MySQL技术内幕（第5版）》，MySQL 5.5

《MySQL5.7从入门到精通》，MySQL 5.7