这是本节的多页打印视图。点击此处打印.

Mysql使用小技巧

1: MySql性能监控工具MyTop
2: mysql之数据库导入导出
3: MySql插入唯一键冲突的三种可选方式
4: mysql之给现有表新增自增字段
5: mysql 新增多列写法
6: MySql查询时间戳日期互转
7: mysql索引过长Specialed key was too long问题记录
8: MySql Timestamp默认值限制问题
9: MySql最大连接数查看与修改
10: MySql表字段修改记录
11: MySql之json_extract函数处理json字段
12: 220707-MySql按时、天、周、月进行数据统计

再实际使用MySql的过程中，可能会用到的小技巧，小手段

1 - MySql性能监控工具MyTop

mysql 性能监控小工具之 mytop

参考： How To Use Mytop to Monitor MySQL Performance

I. 安装与配置

Centos 下可以直接通过yum进行安装

yum install mytop

使用姿势和top命令一样，直接用即可

mytop

可能提示没有权限，如下

# mytop                                                                                            !500
Cannot connect to MySQL server. Please check the:

  * database you specified "test" (default is "test")
  * username you specified "root" (default is "root")
  * password you specified "" (default is "")
  * hostname you specified "localhost" (default is "localhost")
  * port you specified "3306" (default is 3306)
  * socket you specified "" (default is "")

The options my be specified on the command-line or in a ~/.mytop
config file. See the manual (perldoc mytop) for details.

Here's the exact error from DBI. It might help you debug:

Access denied for user 'root'@'localhost' (using password: NO)

配置文件

通过上面的提示，可以添加配置文件

vim ~/.mytop

## 配置信息如

host=localhost
user=root
pass=mypwd
db=dbName
port=3306
# 刷新时间，5s刷新下
delay=5
batchmode=0
color=1
idle=1

使用姿势

直接将密码写到配置文件，可能并不是非常安全，可以如下操作

mytop --prompt
# 然后再窗口内输入密码即可

如果不想添加上面的配置，可以指定参数监控的db

mytop -d dbName --prompt

II. 监控与参数说明

执行上面的命令之后，窗口显示内容如下

MySQL on localhost (5.7.18)                                                     up 345+19:27:20 [18:55:58]
 Queries: 778.2k  qps:    0 Slow:     0.0         Se/In/Up/De(%):    72/00/02/00
             qps now:    1 Slow qps: 0.0  Threads:    3 (   1/   6) 50/00/00/00
 Key Efficiency: 50.0%  Bps in/out:   3.3/163.3   Now in/out:  24.9/ 2.1k

      Id      User         Host/IP         DB      Time    Cmd Query or State
       --      ----         -------         --      ----    --- ----------
      568      root       localhost      story         0  Query show full processlist
      567      root       localhost      story         5  Sleep
      541      root localhost:44568       solo      3529  Sleep

1. 参数说明

第一行

Queries: 778.2k  qps:    0 Slow:     0.0         Se/In/Up/De(%):    72/00/02/00

整体信息：

Queries 服务器处理过的query总数
qps 每秒处理的query数量的平均值
Slow 慢查询总数
Se/In/Up/De(%) Select,Insert,Update,Delete 各自的占比

第二行

             qps now:    1 Slow qps: 0.0  Threads:    3 (   1/   6) 50/00/00/00

当前的qps信息：

qps now 本周期内的每秒处理query的数量
Slow qps 本周期内的每秒慢查询数量
Threads 当前连接线程数量，后面括号内的第一个数字是active状态的线程数量，第二个数字是在线程缓存中的数量
最后一列是本周期内的 Select,Insert,Update,Delete 各自的占比

第三行

Key Efficiency: 50.0%  Bps in/out:   3.3/163.3   Now in/out:  24.9/ 2.1k

Key Efficiency : 表示有多少key是从缓存中读取，而不是从磁盘读取的
Bps in/out : 表示mysql平均的流入流出数据量
Now in/out : 是本周期内的流入流出数据量

剩余

后面的就是线程信息

2. 常用命令

查看活动线程的详细信息，看下这个线程里面正在执行的sql是什么

按F, 然后输入线程ID
然后 e 可以查看sql的情况

查看命令执行的汇总情况

按c
退出按 t

需要更多地命令帮助

按 ?

2 - mysql之数据库导入导出

mysql之数据库导入导出

实际工作中，需要做一下数据库迁移，需要导入导出数据，记录一下mysqldump的简单用法

I. 导出

1. 导出结构不导出数据

只需要数据库的表结构，但是里面的数据不要

mysqldump　--opt　-d　数据库名　-u　root　-p　>　xxx.sql

2. 导出数据不导出结构

数据表结构已有，但是需要里面的数据

mysqldump　-t　数据库名　-uroot　-p　>　xxx.sql

3. 导出数据和表结构

mysqldump　-uroot　-p　-B　数据库名　--table　表名　>　xxx.sql

II. 数据导入

进入数据库，执行

source xxx.sql

3 - MySql插入唯一键冲突的三种可选方式

MySql插入时唯一键冲突的几种处理方式

MySql插入一条记录，结果提示主键冲突，怎么办？

批量插入数据时，发现插入的这批数据中，有某些记录存在唯一键冲突，一个一个跳出来就比较麻烦了，有什么好的办法直接忽略掉冲突的记录么？

下面简单记录三种处理方式

I. 插入时唯一键冲突问题

1. Ignore关键词

某些场景下，我们需要批量插入的数据，某些已经在DB中了，因此我希望在出现冲突时，直接跳过，把能插入的都插入就好，这种情况下，使用ignore关键词就比较合适了

一个实际的case如下

insert ignore into table (xxx, xxx) values (xxx,xxx), (xxx, xxx);

执行截图如下, 注意下面红框中的内容，表示忽略了两条，执行插入成功一条

2. Replace Into方式

如果在批量插入中，存在冲突时，我希望用我的新数据替换旧的数据，这个时候就可以使用replace into了

常用姿势如下

replace into `user` (`id`, `name`, `create_at`, `update_at`) 
values
	(1, 'test', '2018-07-10 18:54:00', '2018-07-10 19:54:52'),
	(2, 'test2', '2018-07-10 18:54:00', '2018-07-10 19:54:52'),
	(3, 'test3', '2018-07-10 18:54:00', '2018-07-10 19:54:52');

执行截图如下，注意红框中，当某条记录冲突之后并修改，则影响行数为2, 其实际过程是

删除冲突数据
插入新的数据

3. ON DUPLICATE KEY UPDATE

在出现冲突时，希望更新某些数据，这个时候就可以在insert语句的最后加上on duplicate key update了

实例如下

insert into `user` (`id`, `name`, `create_at`, `update_at`) values (1, 'test0', '2018-07-10 18:54:00', '2018-07-10 18:54:52') ON DUPLICATE KEY UPDATE `update_at`='2018-07-10 19:58:05';

执行截图如下，这个是在原记录的基础上执行更新指定的value, 比如上面的插入中，当冲突时，我们只更新update_at字段，而name的test0没有更新

4 - mysql之给现有表新增自增字段

I. 问题描述

一张现有的表，没有自增主键，拿的是一串字符串作为的表的主键，实际使用来，并没有什么问题，但是在扫表的时候就不是特别的方便了，

因此希望给这张表新增一个自增的id

因为主键被占用了，所以只能新增一个唯一的自增字段, 可以按照基本的添加字段的方式来实现

ALTER TABLE table_name ADD id INT(11) NOT NULL UNIQUE KEY AUTO_INCREMENT FIRST;

说明

上面的FIRST表示新增的字段放在最前面

5 - mysql 新增多列写法

记录下同时新增多列的sql写法

alter table table_name add (amount decimal(20,8) NOT NULL DEFAULT '0.00000000' COMMENT '数量' , price decimal(20,8) NOT NULL DEFAULT '0.00000000' COMMENT '价格');

用圆括号包含起来即可

6 - MySql查询时间戳日期互转

mysql内部提供了时间戳和日期互转的函数方便直接使用

from_unixtime(time_stamp) : 将时间戳转换为日期
unix_timestamp(date) : 将指定的日期或者日期字符串转换为时间戳

一个简单的实例如下

mysql> select * from Subscribe;
+----+------------------+-----------+--------+------------+------------+-------+---------+
| id | email            | nick      | status | created    | updated    | extra | channel |
+----+------------------+-----------+--------+------------+------------+-------+---------+
|  1 | bangzewu@126.com | 小灰灰    |      1 | 1523008294 | 1523008294 |       |       0 |
|  2 | test@test.com    | 123       |      2 | 1523008453 | 1523008453 |       |       0 |
+----+------------------+-----------+--------+------------+------------+-------+---------+
2 rows in set (0.00 sec)

mysql> select from_unixtime(updated) from Subscribe limit 1;
+------------------------+
| from_unixtime(updated) |
+------------------------+
| 2018-04-06 17:51:34    |
+------------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp(from_unixtime(updated)) from Subscribe limit 1;
+----------------------------------------+
| unix_timestamp(from_unixtime(updated)) |
+----------------------------------------+
|                             1523008294 |
+----------------------------------------+
1 row in set (0.00 sec)

7 - mysql索引过长Specialed key was too long问题记录

在创建要给表的时候遇到一个有意思的问题，提示Specified key was too long; max key length is 767 bytes，从描述上来看，是Key太长，超过了指定的 767字节限制

下面是产生问题的表结构

CREATE TABLE `test_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(1000) NOT NULL DEFAULT '',
  `link` varchar(1000) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

我们可以看到，对于name，我们设置长度为1000可变字符，因为采用utf8mb4编码, 所以它的大小就变成了 1000 * 4 > 767

所以再不修改其他配置的前提下，varchar的长度大小应该是 767 / 4 = 191

有兴趣的同学可以测试下，分别指定name大小为191, 192时，是不是前面的可以创建表成功，后面的创建表失败，并提示错误Specified key was too long; max key length is 767 bytes

解决办法一

使用innodb引擎
启用innodb_large_prefix选项，修改约束扩展至3072字节
重新创建数据库

my.cnf配置

set global innodb_large_prefix=on;
set global innodb_file_per_table=on;
set global innodb_file_format=BARRACUDA;
set global innodb_file_format_max=BARRACUDA;

上面这个3072字节的得出原因如下

我们知道InnoDB一个page的默认大小是16k。由于是Btree组织，要求叶子节点上一个page至少要包含两条记录（否则就退化链表了）。

所以一个记录最多不能超过8k。又由于InnoDB的聚簇索引结构，一个二级索引要包含主键索引，因此每个单个索引不能超过4k （极端情况，pk和某个二级索引都达到这个限制）。

由于需要预留和辅助空间，扣掉后不能超过3500，取个“整数”就是(1024*3)

解决办法二

在创建表的时候，加上 row_format=DYNAMIC

CREATE TABLE `test_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `link` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 row_format=DYNAMIC;

这个参数的作用如下

MySQL 索引只支持767个字节，utf8mb4 每个字符占用4个字节，所以索引最大长度只能为191个字符，即varchar(191)，若想要使用更大的字段，mysql需要设置成支持数据压缩，并且修改表属性 row_format ={DYNAMIC|COMPRESSED}

8 - MySql Timestamp默认值限制问题

今天在往mysql表中新增一列timestamp时，希望设置默认值为0，结果发现居然提示失败，记录一下

问题记录

测试的mysql版本为 5.7.24

创建要给测试的表用来说明

mysql> show create table demo\G
*************************** 1. row ***************************
       Table: demo
Create Table: CREATE TABLE `demo` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `age` int(10) NOT NULL DEFAULT '0',
  `name` varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `UNI_AGE` (`age`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

我们希望在这个表里面，新增一列, 默认值为0（即对应的日期为1970-01-01 00:00:00）

mysql> alter table demo add column test_time timestamp not null default '1970-01-01 00:00:00';
ERROR 1067 (42000): Invalid default value for 'test_time'

直接提示默认值非法，why?

官方说明： https://dev.mysql.com/doc/refman/5.7/en/datetime.html

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.

默认值有限制，要求必须是>=1970-01-01 00:00:01 且 <=2038-01-19 03:14:07

然后我们再测试一下

mysql> alter table demo add column test_time timestamp not null default '1970-01-01 00:00:01';
ERROR 1067 (42000): Invalid default value for 'test_time'

依然是失败!!! why？

注意上面说的时间是utc日期，而我们大中华是utc8

所以我们需要把时间设置为8:00:01

mysql> alter table demo add column test_time timestamp not null default '1970-01-01 08:00:01';
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table demo\G
*************************** 1. row ***************************
       Table: demo
Create Table: CREATE TABLE `demo` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `age` int(10) NOT NULL DEFAULT '0',
  `name` varchar(30) NOT NULL DEFAULT '',
  `test_time` timestamp NOT NULL DEFAULT '1970-01-01 08:00:01',
  PRIMARY KEY (`id`),
  KEY `UNI_AGE` (`age`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4
1 row in set (0.01 sec)

9 - MySql最大连接数查看与修改

原文来自: https://www.cnblogs.com/niuben/p/10834668.html

查看与修改mysql的最大连接数命令如下

-- 查看最大连接数
show variables like '%max_connections%';

-- 修改最大连接数
set GLOBAL max_connections = 1024;

从上面的查看最大连接数的sql语句也可以看到这个属于配置变量，我们可以通过sohw status查看一些常见的信息

参数	说明
`Aborted_clients`	由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。
`Aborted_connects`	尝试已经失败的MySQL服务器的连接的次数。
`Connections`	试图连接MySQL服务器的次数。
`Created_tmp_tables`	当执行语句时，已经被创造了的隐含临时表的数量。
`Delayed_insert_threads`	正在使用的延迟插入处理器线程的数量。
`Delayed_writes`	用INSERTDELAYED写入的行数。
`Delayed_errors`	用INSERTDELAYED写入的发生某些错误(可能重复键值)的行数。
`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`	在INSERTDELAY队列中等待写入的行的数量。
`Open_tables`	打开表的数量。
`Open_files`	打开文件的数量。
`Open_streams`	打开流的数量(主要用于日志记载）
`Opened_tables`	已经打开的表的数量。
`Questions`	发往服务器的查询的数量。
`Slow_queries`	要花超过long_query_time时间的查询数量。
`Threads_connected`	当前打开的连接的数量。
`Threads_running`	不在睡眠的线程数量。
`Uptime`	服务器工作了多少秒

10 - MySql表字段修改记录

mysql修改表数据类型的方式

针对一个已经存在的列，修改类型，主要使用 modify

alter table xxx modify id int(11) unsigned not null auto_increment comment '主键id'

如果是希望新增一列，使用add column

-- after 表示这个新增的列在id这一列之后
alter table xxx add column to_add varchar(11) not null default '' comment '新增的列' after `id`;

删除某一列，使用 drop column

-- 删除 xxx 这一列
alter table drop column xxx;

11 - MySql之json_extract函数处理json字段

在db中存储json格式的数据，相信大家都或多或少的使用过，那么在查询这个json结构中的数据时，有什么好的方法么？取出String之后再代码中进行解析？

接下来本文将介绍一下Mysql5.7+之后提供的json_extract函数，可以通过key查询value值

1. 使用方式

数据存储的数据是json字符串，类型为我们常用的varchar即可

语法:

JSON_EXTRACT(json_doc, path[, path] …)

若json字符串非数组时，可以通过$.字段名来表示查询对应的value

2.使用演示

创建一个测试的表

CREATE TABLE `json_table` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `val` json DEFAULT NULL COMMENT 'json字符串',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入几条数据

insert into `json_table` values (1, '{"name": "一灰灰blog", "age": 18}');
insert into `json_table` values (2, '{"name": "一灰灰blog", "site": "https://blog.hhui.top"}');

查询json串中的name，如下

mysql> select json_extract(`val`, '$.name') from `json_table`;
+-------------------------------+
| json_extract(`val`, '$.name') |
+-------------------------------+
| "一灰灰blog"                  |
| "一灰灰blog"                  |
+-------------------------------+

如果查询的key不在json串中，返回的是null，而不是抛异常

mysql> select json_extract(`val`, '$.name') as `name`, json_extract(`val`, '$.site') as `site` from `json_table`;
+-----------------+-------------------------+
| name            | site                    |
+-----------------+-------------------------+
| "一灰灰blog"    | NULL                    |
| "一灰灰blog"    | "https://blog.hhui.top" |
+-----------------+-------------------------+

接下来再看一下如果为json数组，怎么整

mysql> insert into `json_table` values (3, '[{"name": "一灰灰", "site": "https://spring.hhui.top"}]');


mysql> select json_extract(`val`, '$[0].name') from `json_table` where id = 3;
+----------------------------------+
| json_extract(`val`, '$[0].name') |
+----------------------------------+
| "一灰灰"                         |
+----------------------------------+

除了在查询结果中使用json_extract之外，也可以在查询条件中使用它

mysql> select * from `json_table` where json_extract(`val`, '$.name') = '一灰灰blog';
+----+------------------------------------------------------------+
| id | val                                                        |
+----+------------------------------------------------------------+
|  1 | {"age": 18, "name": "一灰灰blog"}                          |
|  2 | {"name": "一灰灰blog", "site": "https://blog.hhui.top"}    |
+----+------------------------------------------------------------+

3. 小结

本文主要介绍json_extract函数的使用姿势，用于解析字段内value为json串的场景

基本使用姿势

json对象：json_extract(‘db字段’, ‘$.json串key’)
json数组：json_extract(‘db字段’, ‘$[数组下标].json串key’)

12 - 220707-MySql按时、天、周、月进行数据统计

最近遇到一个统计的需求场景，针对db中的数据，看一下每天的数据量情况，由于DB中时间字段采用的是int存的时间戳，所以最开始想到的是直接对时间进行按天取整，然后再Group统计数据；

除此之外，使用DATE_FORMAT函数来处理可能是更简洁的方法了，下面分别介绍下两种方式

1. 时间取整方式

假设现在有一个user表，其中create_time 为 int类型的时间戳，此时我们需要统计每天的新增用户数，第一种方式就是将create_time转换为天为单位的整数，然后group分组之后计数即可

对应的sql如下

select floor(create_time / 86400) as c, count(*) from `user` group by c

使用上面这种方式虽然可以统计出结果，但是显示并不友好，如上面这个c实际上是距离标准起始时间过去的天数；无法直观看到每天的数量情况

2. data_format方式

接下来再介绍一下根据日期格式化这个函数来实现数据统计

函数说明

这个函数通常接收两个参数，使用姿势形如

DATE_FORMAT(date,format)

date: 日期
format: 规定日期/时间的输出格式

注意上面的date，要求是日期格式，可我们现在的数据是int类型，怎么整？

先通过from_unixtime函数来转换为日期，然后再使用data_format来格式化分组，这样就可行了

比如按天统计的sql可以如下

select date_format(from_unixtime(create_time), '%Y-%m-%d') today, count(*) as cnt from user group by today

返回结果形如

today	cnt
2022-07-02	6
2022-07-03	4
2022-07-04	4
2022-07-05	3
2022-07-06	2
2022-07-07	1

如果需要按周统计，也很方便，将format改成 %Y-%u

select date_format(from_unixtime(create_time), '%Y-%u') today, count(*) as cnt from user group by today

返回结果形如

today	cnt
2022-22	27
2022-23	52
2022-24	28
2022-25	33
2022-26	39
2022-27	10

同样按年统计，则将format改成%Y即可

下面给出format对应的取值说明

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T 时间	24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w 周的天（0=星期日	6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位