HHTjim'S 部落格

Mysql中数字类型括号后面的含义

作者：matrix 发布时间：2024-11-30 分类：mysql

设计数据库字段时很少在意这个括号数字的含义，都是大概知道不影响数据存储所以没在意。今天正好看到也就好奇下。

mysql中，INT(5) 和 INT(11)这里的数字（5、11）实际上并不限制数值的存储范围或内存使用，而是显示宽度。
这个显示宽度在字段在带有 ZEROFILL（零填充）才有用

这里不仅限于int类型，也包含tinyint、smallint、mediumint、bigint...

比如 设定字段类型int(5)

    存入1显示为 00001
    存入82显示为 00082
    存入123456显示为 123456


select id,type from table;
******************** 1. row *********************
  id: 1
type: 123456
******************** 2. row *********************
  id: 2
type: 00008
******************** 3. row *********************

int(10)同理，其他数字类型亦是。所以说括号里的数字其实没有任何影响，很多场景实际上不会用到。

可能也是考虑到这点，目前mysql8+的版本如果建表时字段设置了显示宽度,会有Warning。所以不管mysql版本号，新建数字类型字段都不用设置显示宽度，除非你是需要ZEROFILL。

字段的ZEROFILL如下声明：

CREATE TABLE example (
    id INT(5) ZEROFILL NOT NULL,
    name VARCHAR(100)
);

只有这样声明了ZEROFILL才有用

PEACE～

mysql float字段类型数据查询为空问题

作者：matrix 发布时间：2021-12-28 分类：mysql PHP

结论

不要用float、double类型存储浮点数。改用decimal字段类型

过程

之前是知道浮点数最好不要用float类型做存储，手上遇到老项目使用就正好是float字段存储的体重数据，比如51.6这种。

普通的查询没问题，个别数据就出现查询为空的问题。后来发现都是浮点类型数据，排查框架的sql日志到PDO的参数绑定找遍了都没找到根源。还以为是PDO扩展的data_type出错，因为内部sql执行时浮点数的参数绑定是使用PDO::PARAM_STR。

$this->PDOStatement->bindValue(':ThinkBind_1_', 51.6, PDO::PARAM_STR)

虽然字段设置了精度float(10,2)，但是依然有查询为空出现。这就是float精度导致的问题。

吐槽

TP5.1 sql日志输出不准确，和实际执行的不一致！

sql输出日志为where wi=51.6,实际上执行是where wi="51.6",这也增加了排查的难度。

办法

1. 浮点数查询使用like
2. 使用函数比如oncat(wi)=51.6,或者format(wi,2) = format(51.6 ,2)
3. 使用decimal字段类型

参考：

https://www.cnblogs.com/powerwu/articles/8465031.html

https://blog.csdn.net/luccs624061082/article/details/84286253

mysqldump指定数据表导出

作者：matrix 发布时间：2021-03-31 分类：mysql

平时习惯使用mysql客户端工具直接导出表数据，这突然需要导出指定前缀的表反而变得麻烦，因为表非常多但又不想全部选择。

e.g. 导出dict_开头的数据表

查询符合条件的表名

select table_name from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'heal' and table_name like 'dict_%';

执行导出命令

mysqldump --column-statistics=0 -h 127.0.0.1 -P3306 -pPASSWORD  -t heal -uroot --tables dict_union dict_tag > ~/db_script.sql

-P端口号
-p密码
--tables 指定多个数据表

报错 mysqldump: Couldn't execute

mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')                FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'heal' AND TABLE_NAME = 'dict_union';': Unknown table 'COLUMN_STATISTICS' in information_schema

需要添加参数--column-statistics=0

参考：

https://www.cnblogs.com/commissar-Xia/p/10302336.html

https://researchlab.github.io/2017/02/22/mysql-import-export-summary/

快速清空超大数据表

作者：matrix 发布时间：2020-08-31 分类：Python 零零星星

第一次drop超过GB的数据表，没想到竟然会执行的这么慢。尝试过TRUNCATE和DROP都不满意。
后来就直接找到数据库储存的文件来删除，这样比起使用sql语句操作会快得多，但也是危险操作，无法找回。

删除操作脚本

运行环境 python3.7,依赖pymysql,根据自身情况配置变量mysql_data_dir,db_config,table_names,condition_save

fast_drop_table.py

#codeing=utf-8
"""
快速清空超大数据表 保留想要数据
"""
import pymysql
import os

mysql_data_dir = '/mnt/mysql_data/db_name/' #数据库文件所在路径

# 数据库连接配置
db_config = {'host': '127.0.0.1', 'port': 3306, 'user': 'user', 'password': 'password', 'db': 'db_name', 'charset': 'utf8'}

# 需要清空操作的数据表
table_names = [
"com_hhtjim_badata_trades_eos_this_quarter",
"com_hhtjim_badata_trades_eth_this_quarter",
  ]

# 数据表保留的查询条件
condition_save = "timestamp  >  '2020-02-20T00:00:00Z'"
# condition_save = False# 不保留


class Db:
    '''
    简单数据库连接操作类
    '''
    def __init__(self,**kwargs):
        self.connection = pymysql.connect(**kwargs)
        self.cursor = self.connection.cursor()


if __name__ == "__main__":
  mysql = Db(**db_config)
  for table_name in table_names:
    os.link('{}{}.frm'.format(mysql_data_dir,table_name), '{}{}.frm.h'.format(mysql_data_dir,table_name))
    os.link('{}{}.ibd'.format(mysql_data_dir,table_name), '{}{}.ibd.h'.format(mysql_data_dir,table_name))


    mysql.cursor.execute('CREATE TABLE {0}_back like {0}'.format(table_name))
    mysql.connection.commit()


    if condition_save:
      mysql.cursor.execute("INSERT INTO {0}_back SELECT * FROM {0}  WHERE {1} ;".format(table_name,condition_save))
      mysql.connection.commit()


    mysql.cursor.execute("drop table {}".format(table_name))
    mysql.connection.commit()

    mysql.cursor.execute("alter table  {0}_back rename to  {0};".format(table_name))
    mysql.connection.commit()


    os.unlink('{}{}.frm.h'.format(mysql_data_dir,table_name))
    os.unlink('{}{}.ibd.h'.format(mysql_data_dir,table_name))

    print('succeed: {}'.format(table_name))

具体步骤

### 找到frm，ibd文件

根据数据库存储路径找到需要删除的表名的frm，ibd文件。

### 建立硬连接
$ ln mytable.ibd  mytable.ibd.h
$ ln mytable.frm  mytable.frm.h


### 备份表结构
CREATE TABLE mytable_back like mytable;

### 备份想要保留的数据
INSERT INTO mytable_back SELECT * FROM mytable  WHERE timestamp  >  '2020-02-27T00:00:00Z' ;

### 删除旧表
drop table mytable;

### 修改备份表名字
alter table  mytable_back rename to  mytable;


### 删除硬连接
$ rm -f  mytable.frm.h  mytable.ibd.h

参考：
https://blog.csdn.net/weixin_34034261/article/details/86250223

mysql定时任务删除数据

作者：matrix 发布时间：2020-03-31 分类：mysql

需要每天定时删除旧数据 避免占用大量磁盘空间

用到mysql事件调度器event_scheduler

SHOW VARIABLES LIKE 'event_scheduler'; # 查看开关

SET GLOBAL event_scheduler = ON; #打开事件调度器

#查看事件
SHOW EVENTS;# 其中 EVENT_DEFINITION字段为事件执行的sql语句
select EVENT_NAME,LAST_EXECUTED from information_schema.EVENTS;
DROP EVENT [IF EXISTS] event_name;#删除事件

定时删除旧数据

#每天定时清理 只保留最近30天数据schedule_del_depth5_eos_quarter表

CREATE EVENT IF NOT EXISTS schedule_del_depth5_eos_quarter
ON SCHEDULE EVERY 1 DAY 
STARTS concat(CURRENT_DATE,' 00:00:00') 
DO
delete from schedule_del_depth5_eos_quarter where timestamp < DATE_FORMAT( date_sub(curdate(),interval 30 day)  ,'%Y-%m-%d');

参考：

https://www.cnblogs.com/geaozhang/p/6821692.html

https://blog.csdn.net/beyondlpf/article/details/39672599

https://blog.csdn.net/small____fish/article/details/7621160

https://blog.csdn.net/qq_33083551/article/details/88789311

无法安装python3的连续报错-mysql include软链接问题

作者：matrix 发布时间：2019-03-12 分类：Python

虚拟机ubuntu中自带了Python2，但是想要使用python3需要单独安装。使用add-apt-repository来添加ppa源安装提示command not found,之后进行安装操作sudo apt-get install software-properties-common，错误继续：

$ sudo apt-get install software-properties-common
Reading package lists... Done
Building dependency tree... 50%
Building dependency tree       
Reading state information... Done
You might want to run 'apt-get -f install' to correct these:
The following packages have unmet dependencies:
 libmysql++-dev : Depends: libmysqlclient-dev but it is not going to be installed
 software-properties-common : Depends: python3:any (>= 3.3.2-2~)
                              Depends: python3 but it is not going to be installed
                              Depends: python3-gi but it is not going to be installed
                              Depends: gir1.2-glib-2.0 but it is not going to be installed
                              Depends: python-apt-common (>= 0.9) but it is not going to be installed
                              Depends: python3-dbus but it is not going to be installed
                              Depends: python3-software-properties (= 0.96.20.8) but it is not going to be installed
E: Unmet dependencies. Try 'apt-get -f install' with no packages (or specify a solution).

继续apt-get update & upgrade & 更换国内源，后面使用apt-get install Python3也是相同错误，无济于事。
错误跟踪到libmysql++-dev依赖问题，遂重新安装sudo apt-get install libmysqlclient-dev:

$ sudo apt-get install libmysqlclient-dev
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following NEW packages will be installed:
  libmysqlclient-dev
0 upgraded, 1 newly installed, 0 to remove and 163 not upgraded.
4 not fully installed or removed.
Need to get 0 B/1,167 kB of archives.
After this operation, 7,040 kB of additional disk space will be used.
debconf: delaying package configuration, since apt-utils is not installed
(Reading database ... 51891 files and directories currently installed.)
Preparing to unpack .../libmysqlclient-dev_5.7.25-0ubuntu0.16.04.2_amd64.deb ...
Unpacking libmysqlclient-dev (5.7.25-0ubuntu0.16.04.2) ...
dpkg: error processing archive /var/cache/apt/archives/libmysqlclient-dev_5.7.25-0ubuntu0.16.04.2_amd64.deb (--unpack):
 unable to install new version of '/usr/include/mysql/mysql/plugin_ftparser.h': No such file or directory
Errors were encountered while processing:
 /var/cache/apt/archives/libmysqlclient-dev_5.7.25-0ubuntu0.16.04.2_amd64.deb
E: Sub-process /usr/bin/dpkg returned an error code (1)

尝试手动安装deb：sudo dpkg -i /var/cache/apt/archives/libmysqlclient-dev_5.7.25-0ubuntu0.16.04.2_amd64.deb失败：

unable to install new version of '/usr/include/mysql/mysql/plugin_ftparser.h': No such file or directory

根源问题

/usr/include/mysql/mysql/plugin_ftparser.h不存在
开始手动查找

$ sudo find / | grep plugin_ftparser.h

该文件只存在与/usr/local/mysql/include/目录中，ls /usr/include/mysql -al查看列表就会发现mysql的软链接出现问题导致的。

解决

重新生成mysql include软连接

$ ln -s /usr/local/mysql/include /usr/include/mysql/mysql #重新生成软连接

此虚拟机中的mysql是之前安装LNMP搭建PHP环境而创建的，很有可能和这个有关系。

参考：
https://askubuntu.com/questions/629448/file-mysql-h-cant-be-found
https://askubuntu.com/questions/773079/fixing-broken-packages-in-ubuntu-repost

PEACE～

xiaomi开源SQL优化建议工具 - soar

作者：matrix 发布时间：2018-10-29 分类：零零星星

SOAR -- sql Optimizer And Rewriter 由小米运维 DBA 团队开发的SQL 智能优化与改写工具20181021宣布开源。
github：https://github.com/xiaomi/soar

安装说明：https://github.com/XiaoMi/soar/blob/master/doc/install.md

测试环境：ubuntu 16.04

安装GO

apt-get安装失败改用
源码下载：https://GOlang.GOogle.cn/dl/
配置环境变量

解压：
> sudo tar -C /usr/local -xzf go1.11.1.linux-amd64.tar.gz

全局用户的环境变量：
> sudo vi /etc/profile

末尾添加:
export PATH=$PATH:/usr/local/go/bin

go版本查看：

> go version

source更新环境变量:

source /etc/profile

还需要配置GOPATH环境变量：表示go的工作目录 USER_NAME 为用户名
export GOPATH="/home/USER_NAME/go"

安装soar

> go get -d github.com/XiaoMi/soar
> cd ${GOPATH}/src/github.com/XiaoMi/soar && make

若安装顺利，最终会显示success。否则 build error

我前几次安装都失败

go build github.com/pingcap/tidb/parser: /usr/local/go/pkg/tool/linux_amd64/compile: signal: killed 
Makefile:69: recipe for target 'build' failed

之后google找到帖子 有人说是vps内存太低导致的，遂重启了下Ubuntu 重新make。bingo～

安装成功之后会发现~/go/src/github.com/XiaoMi/soar多出一个 soar文件。
执行测试：

> cd ~/go/src/github.com/XiaoMi/soar
> echo 'select * from film' | ./soar

使用soar

常用命令：https://github.com/XiaoMi/soar/blob/master/doc/cheatsheet.md

打印所有的启发式规则¹

$ soar -list-heuristic-rules

打印支持的报告格式

$ soar -list-report-types

以指定格式输出报告

$ soar -report-type json

语法检查工具

$ echo "select * from tb" | soar -only-syntax-check
$ echo $?
0

$ echo "select * fromtb" | soar -only-syntax-check
At SQL 0 : syntax error at position 16 near 'fromtb'
$ echo $?
1

慢日志进行分析示例

$ pt-query-digest slow.log > slow.log.digest
# parse pt-query-digest's output which example script
$ python2.7 doc/example/digest_pt.py slow.log.digest > slow.md

SQL指纹

$ echo "select * from film where col='abc'" | soar -report-type=fingerprint

输出

select * from film where col=?

将UPDATE/DELETE/INSERT语法转为SELECT

$ echo "update film set title = 'abc'" | soar -rewrite-rules dml2select,delimiter  -report-type rewrite

输出

select * from film;

合并多条ALTER语句

$ echo "alter table tb add column a int; alter table tb add column b int;" | soar -report-type rewrite -rewrite-rules mergealter

输出

ALTER TABLE `tb` add column a int, add column b int ;

SQL美化

$ echo "select * from tbl where col = 'val'" | ./soar -report-type=pretty

输出

SELECT
  *
FROM
  tbl
WHERE
  col  = 'val';

EXPLAIN信息分析报告

$ soar -report-type explain-digest << EOF
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | film  | ALL  | NULL          | NULL | NULL    | NULL | 1131 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
EOF

##  Explain信息

| id | select\_type | table | partitions | type | possible_keys | key | key\_len | ref | rows | filtered | scalability | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1  | SIMPLE | *film* | NULL | ALL | NULL | NULL | NULL | NULL | 0 | 0.00% | ☠️ **O(n)** |  |


### Explain信息解读

#### SelectType信息解读

* **SIMPLE**: 简单SELECT(不使用UNION或子查询等).

#### Type信息解读

* ☠️ **ALL**: 最坏的情况, 从头到尾全表扫描.

markdown转HTML

通过指定-report-css, -report-javascript, -markdown-extensions, -markdown-html-flags这些参数，你还可以控制HTML的显示格式。

$ cat test.md | soar -report-type md2html > test.html

PEACE～

参考：
https://github.com/beego/wetalk/issues/32
https://www.oschina.net/news/101034/xiaomi-opensource-soar
https://juejin.im/entry/5bbf21fde51d450e61605d99

mysql的分组排序limit问题

作者：matrix 发布时间：2018-05-03 分类：零零星星

业务要求按照type，city分组，然后各取前面的100条数据输出，网上找到了类似的需求直接sql语句就可以解决。

测试表结构

CREATE TABLE `esc_catch` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`city` varchar(20) NOT NULL DEFAULT '' COMMENT '城市',
`price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '报价',
`type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '1,2',
`add_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '添加时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB   DEFAULT CHARSET=utf8mb4

解决办法

set @row=0;set @city=''; set @type='';

select a.city,a.price,a.type ,from_unixtime(a.add_time,'%Y-%m-%d %H:%i:%s') as add_time from esc_catch as a
inner join (

SELECT id,add_time, case when (@city = city and @type= type) then @row:=@row+1 else @row:=1 end as rownum, @city:=city as city , @type:=type as type FROM esc_catch order by type,city,add_time desc

) as b on b.id = a.id
where b.rownum>=100 order by b.type,b.city ;

说明：

头部事先声明变量
row 用于统计指定分组下出现的次数，
city和type是分组条件

核心在于inner join的的临时表操作，其中使用变量操作追加rownum字段
如果变量city，type值等同于临时表的同名字段则该行数据排序下标row++,否则为1

@city:=city as city , @type:=type as type 表示给每行数据的字段值赋给变量

之后在inner join内联表 之后使用自定义的rownum字段b.rownum<=100进行数量条件的限制即可，最后order by 操作便于查看数据

参考：

https://blog.csdn.net/ylqmf/article/details/39005949

https://blog.csdn.net/woshihaiyong168/article/details/78803655

https://zhidao.baidu.com/question/618263527798895012.html

PEACE~