MySQL中truncate函数自动转型的精度问题

MySQL中truncate函数的精度问题

truncate函数

TRUNCATE(X,D) 是MySQL自带的一个系统函数。
其中,X是数值,D是保留小数的位数。
其作用就是按照小数位数,进行数值截取(此处的截取是按保留位数直接进行截取,没有四舍五入)。

现在有这样一个需求,某个表有一个varchar类型的字段,字段内存了一串浮点数。 现在我想要截取到该字段的小数点后两位。

得到的结果毫无疑问,应该是5.02,结果也确实为5.02

那下面这条SQL的结果,你能猜到么?

你可能会说,这还不简单,肯定还是5.02啊。

可是,神奇的一幕出现了,该SQL的执行结果为5.01。 为什么会少了0.1呢,这时就要说到MySQL的TRUNCATE函数做了什么。 因为TRUNCATE函数会对varchar类型做自动转型,产生了精度损失。 在转型后得到的结果其实是5.019999999。然后再做截取,那么结果自然是5.01了。

下面来执行这条SQL

由于没有使用varchar类型,所以TRUNCATE函数没有做自动转型操作,所以并没有产生精度损失,结果为5.02.

cast函数

cast是一种数据类型转换的函数,函数将任何类型的值转换为具有指定类型的值,语法格式如下所示:

CAST ( expression  AS  data_type)

  • expression:任何有效的MySQL表达式或者一些字符串数据。
  • AS:用于分隔两个参数,在AS之前的是要处理的数据,AS之后是要转换的数据类型。
  • data_type:系统所提供的数据类型,这里不能使用用户定义的数据类型。
  • MySQL所能使用的可以是以下类型之一:CHAR(字符型)、DATE(日期)、TIME(时间)、DATETIME(日期时间型)、DECIMAL(浮点数 float)、SIGNED(整数 int)。

依然用5.02这个数字举例:

看似没什么问题,结果也确实是5.02。 但如果使用的是5.029呢?

你会发现,结果变成5.03了,没错,四舍五入了。 执行出来的结果和truncate函数的结果不尽相同,所以也并不能完美满足一开始提出的需求。

总结

这个问题产生的根本问题是使用了varchar类型来存浮点数,并且还需要在数据库内做运算导致的。
我认为解决这个问题的最好时机是在设计时,使用浮点类型来存储该字段。

0

MySQL 官方Docker镜像的使用

Docker镜像是用来创建容器的,我们可以基于官方提供的镜像或自已构建的镜像来创建容器。https://hub.docker.com/_/mysql/是Docker及MySQL提供、维护的一个官方镜像,我们可以基于该镜像构建自己的MySQL数据库镜像,也可以直接使用这个镜像创建MySQL数据库容器。下面介绍MySQL 官方Docker镜像的使用。

MySQL 官方Docker镜像的使用
MySQL 官方Docker镜像的使用

1. 启动一个mysql服务器实例

使用mysql镜像创建或启动MySQL容器时,可以先将镜像下载到本地:

也可以直接使用以下命令来启动MySQL实例:

这样,我们就创建了一个名为sww-mysql的MySQL数据库服务器容器实例。在创建数据库时,通过环境变量MYSQL_ROOT_PASSWORD设置数据库的root密码,还通过5.7标签指定了所使用的镜像版本。

容器创建完成后,可以通过docker ps命令看到所创建的MySQL容器实例:

2. 在其它Docker容器应用中连接MySQL

在这个镜像中,导出的是MySQL的标准端口3306。这样我们就可以在需要访问MySQL服务器的容器中,使用--link参数通过容器链接的方式,将MySQL服务器容器实例连接到包含了需要使用MySQL的应用容器中。

使用容器连接的示例如下:

注意:以上示例中的application-that-uses-镜像并不存在,仅为操作演示,下同。

除了进行容器连接的方式在其它容器中访问MySQL服务器容器外,还可以通过以下两种方式访问MySQL数据库服务器容器:

  • 在创建MySQL服务器容器实例时通过-p-P参数将数据库服务器端口映射到宿主机,再直接通过宿主机进行访问。这种方式较为简单,但需要向外暴露数据库端口。
  • 能过Docker网络(Networking)进行连接。这种方式操作较为复杂,但更为灵活,可以适用于更加复杂的网络环境。

3. MySQL命令行客户端连接MySQL

在前面创建的MySQL服务器容器中,我们并没有向外暴露访问端口,我们可以通过以下方式启动命令行客户端,并基于命令行客户端对数据库服务器进行管理。

运行另一个MySQL交互式容器,该容器会在运行后启动mysql命令行客户端:

如果需要通过非Docker的方式,或远程访问MySQL服务器容器,就可以在创建容器时通过-p-P与宿主机进行端口绑定,之后就可以像普通MySQL服务器那样进行访问或操作。

4. 在Shell中访问容器及日志查看

docker exec命令使我们可以在Docker容器内部执行命令,我们可以通过以下方式与mysql容器建立一个shell连接:

而MySQL服务器日志,可以直接通过Docker容器日志访问(实时日志查看可以添加-f参数):

5. 使用自定义MySQL配置文件

默认情况下,MySQL的启动配置文件是/etc/mysql/my.cnf,而/etc/mysql/conf.d目录下的存在任何.cnf格式的文件时,都会使用该文件中配置项替换默认配置。

因此,如果要使用自定义配置,可以在宿主机创建一个配置文件,然后在创建容器时通过-v参数,以数据卷的方式将自定义配置挂载到mysql容器的/etc/mysql/conf.d目录下。

如,在宿主机中存在/my/custom/config-file.cnf配置文件,这时就可以通过以下方式启动MySQL容器:

以上示例会启动一个名为sww-mysql的MySQL服务器容器,该文件启动时会同时使用/etc/mysql/my.cnf/etc/mysql/conf.d/config-file.cnf中的配置。

不使用cnf文件的配置方式

除使用.cnf文件进行配置外,还可以在启动容器通过参数的形式将配置传递给mysqld

如,启动一个MySQL服务器容器,并使用UTF-8(utf8mb4)格式的表编码:

详细配置参数可以通过以下命令查看:

6. 环境变量

当启动mysql容器时,我们可以向docker run命令传入一或多个环境变量来调整MySQL实例的配置。可设置的环境变量有:

  • MYSQL_ROOT_PASSWORD:必须。用于设置MySQLroot用户的密码
  • MYSQL_DATABASE:可选。用于指定镜像启动容器时要创建的数据库。如果提供了用户/密码,则会将该用户做为此数据库的超级用户。
  • MYSQL_USERMYSQL_PASSWORD:可选。用于创建一个新用户并设置密码。
  • MYSQL_ALLOW_EMPTY_PASSWORD:可选。设置为yes时,则可以使用空密码登录
  • MYSQL_RANDOM_ROOT_PASSWORD:可选。设置为yes时会为root用户设置一个随机密码(使用pwgen),所生成的随机密码会被输出到stdout
  • MYSQL_ONETIME_PASSWORD:可选。为root用户指定一个一次性密码,该密码会在用户首次登录时强制修改

7. 关于数据存储

在使用 mysql镜像创建MySQL容器时,数据库数据存储可能会有以下两种方式:

  • 数据卷容器:使用Docker默认的数据管理方式来管理数据库的数据存储,在这种方式下,数据库文件会被写入数据库的内部。这种方式对于用户非常简单,缺点是很在宿主机上找到所存储的数据。
  • 外部数据卷:在宿主机创建一个数据目录,再将数据目录挂载到容器内部。这种方式可以很方便的在宿主机上找到并进行数据管理,但需要确保数据目录的存在。

当使用外部数据卷时,假在宿主机有/my/own/datadir目录,我们可以像下面这样启动mysql容器,并将目录挂载到容器内:

其中,-v /my/own/datadir:/var/lib/mysql是数据卷的挂载,表示将宿主机的/my/own/datadir目录挂载到容器内的/var/lib/mysql目录。这个目录是MySQL的默认数据目录,当使用自定义配置时,应该也做相应的修改。

数据库备份

在非Docker环境下使用的MySQL备份工具,大多数在容器环境下仍然可用,只要其能访问mysqld服务器即可。

下面是一个通过docker exec来对mysql容器中的数据库执行备份的示例:

0

mysql全文模糊搜索MATCH AGAINST方法

mysql中模糊搜索通常用like ‘%keyword%’ 来进行模糊搜索,例如

select * from table where title like ‘%唠吧小站%’

MySQL 4.x以上提供了全文检索支持 MATCH ……AGAINST 模式(不区分大小写)

建立全文索引的表的存储引擎类型必须为MyISAM

问题是match   against对中文模糊搜索支持不是太好

新建一个utf8 MyISAM类型的表并建立一个全文索引  :

其中FULLTEXT(title, body) 给title和body这两列建立全文索引,之后检索的时候注意必须同时指定这两列。

给这个表添加点测试数据

3. 全文检索测试

注意 MATCH (title,body) 里面的值必须是前面建立全文索引的两个字段不能少。

 

mysql 默认支持全文检索的字符长度是4,可以用SHOW VARIABLES LIKE 'ft_min_word_len' 来查看指定的字符长度,也可以在mysql配置文件my.ini 更改最小字符长度,方法是在my.ini 增加一行 比如:ft_min_word_len = 2,改完后重启mysql即可。

另外,MySQL还会计算一个词的权值,以决定是否出现在结果集中,具体如下:

mysql在集和查询中的对每个合适的词都会先计算它们的权重,一个出现在多个文档中的词将有较低的权重(可能甚至有一个零权重),因为在这个特定的集中,它有较低的语义值。否则,如果词是较少的,它将得到一个较高的权重,mysql默认的阀值是50%,上面‘you’在每个文档都出现,因此是100%,只有低于50%的才会出现在结果集中。

全文检索语法

+ 表示AND,即必须包含。- 表示NOT,即不包含。

apple和banana之间是空格,空格表示OR,即至少包含apple、banana中的一个。

必须包含apple,但是如果同时也包含banana则会获得更高的权重。

~ 是我们熟悉的异或运算符。返回的记录必须包含apple,但是如果同时也包含banana会降低权重。但是它没有 +apple -banana 严格,因为后者如果包含banana压根就不返回。

返回同时包含apple和banana或者同时包含apple和orange的记录。但是同时包含apple和banana的记录的权重高于同时包含apple和orange的记录。

0

MySQL分库分表的分页查询解决方案

问题的提出

我们知道,当我们的数据量达到一定数量时,需要将数据表进行水平拆分,从而满足大量数据的存储和查询,保证系统的可用性,但同时会出现另外一个问题就是,如果业务要查询“最近注册的第3页用户”,该如何实现呢?单库上,可以通过简单的sql实现分页查询。

分库分表后变成两个库后,分库依据是user_id,排序依据是time,单个分数据库层失去了time排序的全局视野,如果同样需要实现分页查询时该怎么办呢?有什么比较好的MySQL分库分表的分页查询解决方案呢?

全局视野法

MySQL分库分表的分页查询解决方案

正常来讲,不管哪一个分库的第3页都不一定有全局第3页的所有数据,例如一下三种情况:

情况一:两个分库按照时间排序,数据各占一半,则每页取offset和limit的一般数据回来合并就可以了
MySQL分库分表的分页查询解决方案
情况二:所有数据都在一个库上,则取一个库的所有数据回来就可以了
MySQL分库分表的分页查询解决方案
情况三,那么一般情况是,每个分库的数据数据是随机的,但是一定是在全局offset=600之内
MySQL分库分表的分页查询解决方案

由于不清楚到底是哪种情况,所以必须每个库都返回3页数据,所得到的6页数据在服务层进行内存排序,得到数据全局视野,再取第3页数据,便能够得到想要的全局分页数据。

这种方法缺点是:当查询的页数增大时,每个分库所需返回的数据也越来成倍增加,降低了查询的性能

业务折中

第一种折中的方案是

对全局视野法的一种优化,即禁用制定页数的分页查询,必须通过下一页来实现分页查询的页数跳转,并且在每次查询下一页时将上一页的最大排序字段的值带上(这里就是时间time),这样在每个分库查询数据时待上这个条件,可以优化查询速率。

第二种折中的方案是

数据库分库-数据均衡原理

使用patition key进行分库,在数据量较大,数据分布足够随机的情况下,各分库所有非patition key属性,在各个分库上的数据分布,统计概率情况是一致的。

例如,在uid随机的情况下,使用uid取模分两库,db0和db1:

(1)性别属性,如果db0库上的男性用户占比70%,则db1上男性用户占比也应为70%

(2)年龄属性,如果db0库上18-28岁少女用户比例占比15%,则db1上少女用户比例也应为15%

(3)时间属性,如果db0库上每天10:00之前登录的用户占比为20%,则db1上应该是相同的统计规律

利用这一原理,要查询全局100页数据,offset 9900 limit 100改写为offset 4950 limit 50,每个分库偏移4950(一半),获取50条数据(半页),得到的数据集的并集,基本能够认为,是全局数据的offset 9900 limit 100的数据,当然,这一页数据的精度,并不是精准的。

根据实际业务经验,用户都要查询第100页网页、帖子、邮件的数据了,这一页数据的精准性损失,业务上往往是可以接受的,但此时技术方案的复杂度便大大降低了,既不需要返回更多的数据,也不需要进行服务内存排序了。

二次查找法

有没有一种方法既能满足业务要求,并且不需要折中,性能还高的方法呢?
接下来介绍一种“二次查找法”,不知道能不能讲的明白,我尽量吧。

为了方便举例,假设一页只有5条数据,查询第200页的SQL语句为select * from T order by time offset 1000 limit 5;

分五步:

1. 将select * from T order by time offset 1000 limit 5; 优化成select * from T order by time offset 500 limit 5,注意这里的500=1000/分表数量,并将这个sql下发至每个分库分表中执行,每个分库返回这个sql执行的结果。

2. 找到所有分库返回结果的time的最小值

MySQL分库分表的分页查询解决方案
第一个库,5条数据的time最小值是1487501123
第二个库,5条数据的time最小值是1487501223

故,三页数据中,time最小值来自第一个库,time_min=1487501123,这个过程只需要比较各个分库第一条数据,时间复杂度很低

3. 查询二次改写

第一次改写的SQL语句是select * from T order by time offset 500 limit 5

第二次要改写成一个between语句,between的起点是time_min,between的终点是原来每个分库各自返回数据的最大值:

第一个分库,第一次返回数据的最大值是1487501523
所以查询改写为select * from T order by time where time between time_min and 1487501523

第二个分库,第一次返回数据的最大值是1487501699
所以查询改写为select * from T order by time where time between time_min and 1487501699

MySQL分库分表的分页查询解决方案

从上面图片可以看出,DB1比第一次查出来的数据多了两行,应为查询的范围扩大了

4. 计算time_min这条记录在全局的offset

根据第一步查询的sqlselect * from T order by time offset 500 limit ,我们知道每个库的offset值了,将DB0中的最小time的数据虚拟到DB1中推算在DB1中的offset值=497
MySQL分库分表的分页查询解决方案
从而我们得知time_min这条记录在全局的offset值=500+497=997

5. 根据第二次查询出来的结果集,在内存中作排序,已知time_min在全局中的offset=997,那么结果集排序之后也能推算出offset=1000所在的记录,从而获得sqlselect * from T order by time offset 1000 limit 5的分页查询记录(图片黄色部分)

MySQL分库分表的分页查询解决方案

总结:可以精确的返回业务所需数据,每次返回的数据量都非常小,不会随着翻页增加数据的返回量。

+2

MySQL中truncate函数自动转型的精度问题

truncate函数

TRUNCATE(X,D) 是MySQL自带的一个系统函数。
其中,X是数值,D是保留小数的位数。
其作用就是按照小数位数,进行数值截取(此处的截取是按保留位数直接进行截取,没有四舍五入)。

现在有这样一个需求,某个表有一个varchar类型的字段,字段内存了一串浮点数。 现在我想要截取到该字段的小数点后两位。

得到的结果毫无疑问,应该是5.02,结果也确实为5.02

那下面这条SQL的结果,你能猜到么?

你可能会说,这还不简单,肯定还是5.02啊。

可是,神奇的一幕出现了,该SQL的执行结果为5.01。 为什么会少了0.1呢,这时就要说到MySQL的TRUNCATE函数做了什么。 因为TRUNCATE函数会对varchar类型做自动转型,产生了精度损失。 在转型后得到的结果其实是5.019999999。然后再做截取,那么结果自然是5.01了。

下面来执行这条SQL

由于没有使用varchar类型,所以TRUNCATE函数没有做自动转型操作,所以并没有产生精度损失,结果为5.02.

cast函数

cast是一种数据类型转换的函数,函数将任何类型的值转换为具有指定类型的值,语法格式如下所示:

CAST ( expression  AS  data_type)

  • expression:任何有效的MySQL表达式或者一些字符串数据。
  • AS:用于分隔两个参数,在AS之前的是要处理的数据,AS之后是要转换的数据类型。
  • data_type:系统所提供的数据类型,这里不能使用用户定义的数据类型。
  • MySQL所能使用的可以是以下类型之一:CHAR(字符型)、DATE(日期)、TIME(时间)、DATETIME(日期时间型)、DECIMAL(浮点数 float)、SIGNED(整数 int)。

依然用5.02这个数字举例:

看似没什么问题,结果也确实是5.02。 但如果使用的是5.029呢?

你会发现,结果变成5.03了,没错,四舍五入了。 执行出来的结果和truncate函数的结果不尽相同,所以也并不能完美满足一开始提出的需求。

总结

这个问题产生的根本问题是使用了varchar类型来存浮点数,并且还需要在数据库内做运算导致的。
我认为解决这个问题的最好时机是在设计时,使用浮点类型来存储该字段。

0

sql分割字符串字段为结果集(mysql)

需求说明

有张表 tb_work_content 里面有个字段 work_method ,里面存放的是json格式的字符串,例如 ‘‘[‘钻具分级检测’,’油井场设备检测’]’’, 现在需要sql查出来的结果集是这样的

0

sql

说明

两个REPLACE是把字符串里的 ”和[]去掉

+1

MySQL 分库分表的方式总结

对于分库分表来说,具体有两种方式:垂直拆分和水平拆分
垂直拆分主要是业务的细化和独立,和业务联系比较密切。所以本文只讨论更通用的水平拆分。

为什么分库分表

  1. 降低单机 MySQL 的性能
  2. 降低单表或者单库的数据量,减少数据库的查询压力
  3. 突破单机的容量限制

分库分表的方式

  1. 范围区分(range):按月\按区\按其他的等特殊的属性维度进行分片
  2. 预定义范围:预估有多少数据的容量,对数据进行范围的分配,0-100->A 101-200->B
  3. 取模 Hash:对指定的字段进行取模运算,匹配对应的库和表。

分库分表带来的问题

  1. 数据的维护成本高
  2. 跨库的业务join
  3. 分布式事务的性能低下
  4. 自增 id 的生成问题
  5. 非分片字段查询的轮询的浪费
  6. 多节点排序问题

分库分表的中间件

对于分库分表的中间件有很多,Shardingsphere,Tddl,MyCat,cobar。从架构上分,主要分为两种:JDBC应用方式Proxy模式

JDBC应用模式是基于客户端的分片,有客户端根据Sql和规则,决定具体执行的 sql 的服务器。代表有Shardingsphere,Tddl

JDBC应用模式 优点:

  1. 性能好
  2. 支持跨数据库(mysql oralce mssq)

缺点:

  1. 不能跨语言
  2. 对开发不够友好,增加开发难度

Proxy模式 代理模式是基于 MySQL 做一层转发代理,有代理根据规则来分发具体的SQL 到服务器上。

优点:

  1. 跨语言
  2. 开发无感知

缺点:

  1. 性能比较差,增加了网络请求
  2. 不支持跨数据库
0

部署在AWS-EC2上的Java程序无法连接AWS-RDS MySQL 数据库的问题解决

问题

我在EC2实例中放了个Java程序。当我执行 java -jar app.jar 命令时,SpringBoot应用程序能正常启动,但连接MySQL RDS数据库时却失败了。

已经打开了应用程序正在运行的端口(8090)和MySql端口(3306),用于入站和出站流量:

错误日志

解决

检查EC2实例和RDS实例的安全组(SG)配置。

可以通过EC2仪表板/ RDS仪表板->单击实例并查看“安全组”描述来进行检查,也可以单击“设置”图标(“显示/隐藏”列)并勾选“安全组”。

在RDS的SG配置中:确保已启用从EC2实例的SG到端口3306的访问。可以通过将EC2实例的SG ID作为“自定义IP”值放入配置的“源”字段中来进行此操作。有关更多详细信息,参考下面。

使用mysql命令行测试EC2实例和RDS之间的连接。

参考

VPC IntroductionSecurity in your VPCScenarios for Accessing a DB Instance in a VPC

0

mysql大数据量分页查询时的优化方法

mysql的limit分页查询,会随着偏移量的增大,效率会急剧下降。

查询device表,偏移量分别是10,100,1000,10000,然后测试查询时间。

如果直接把offset设置为40W,足足需要三秒多。

那么,有什么优化方法呢?

利用表的覆盖索引来加速分页查询优化

查询的语句中,如果只包含了索引列,那么查询就会非常快,因为利用覆盖索引查询有算法优化,并且数据就在索引上,不需要再查找相关数据地址了,而且mysql中也有索引缓存。

id字段是主键,索引就是主键索引。我们可以把语句改成这样:

可以看到速度提升了100多倍。

那其实大多数场景,我们不光是只查询id的,那如果要 select * 该怎么办呢?

一种是 >= 的写法

另一种是 join 写法

这两种写法其实原理一样,只要掌握了这些mysql大数据量分页查询时的优化方法,就再也不怕客户嫌查询慢了。

0