mysql number类型引号问题

背景

 这几天在做数据自动化同步测试过程,发现一个诡异的现象。一批100条语句的更新过程中,同步到目标库去执行,总会有几条记录出现更新失败。

原因

1. 查看了同步过程中的执行日志,也米有啥特别明显的问题,单就是update affect = 0 。

2. 问题的查找方式也是比较简单,针对底层执行的update语句,挨个字段确认,到底是哪一个字段影响了记录的定位。 最后发现是一个Decimal(19,8)的字段类型。

3. debug跟踪了下对应的mysql driver代码,发现针对setBigDecimal类型数据处理时,多了个单引号,字段内容就变成了 '1234.12312'.  测试过程中手工去除引号,就可以正常的update. 

 

测试

1.1.创建表
2.mysql> create table ljhtable2 ( ID int(11) auto_increment , NUMBER_VALUES decimal(19,8) , CONSTRAINT ljhtable2_pk PRIMARY KEY(id) );
3.
4.2. 插入记录
5.mysql> insert into ljhtable2 values(1, 4005747665.56665202);

查询

1.mysql> select * from ljhtable2 ;
2.+----+---------------------+
3.| ID | NUMBER_VALUES       |
4.+----+---------------------+
5.|  1 | 4005747665.56665202 |
6.+----+---------------------+
7.1 row in set (0.00 sec)
8.
9.mysql> select * from ljhtable2 where number_values = '4005747665.56665202'; #加了引号就无法查询到
10.Empty set (0.00 sec)
11.
12.mysql> select * from ljhtable2 where number_values = 4005747665.56665202;
13.+----+---------------------+
14.| ID | NUMBER_VALUES       |
15.+----+---------------------+
16.|  1 | 4005747665.56665202 |
17.+----+---------------------+
18.1 row in set (0.00 sec)

深度分析

1. 资讯了下公司的资深的mysql DBA,针对加引号的处理,目前mysql的版本存在一些bug,我们使用的mysql 版本(5.1.41),所以只能通过mysql driver层面进行修复。 

2. mysql driver我们使用了server-side preparestatment + batch处理,问题就出在这里 (参数介绍: mysql几个参数(编码,预编译,批处理) )

 

这是我们使用的mysql driver参数

1.dbcpDs.addConnectionProperty("useServerPrepStmts", "true");
2.dbcpDs.addConnectionProperty("rewriteBatchedStatements", "true");

对应server-side的prepare实现类:ServerPreparedStatement .  

通过debug分析,在ServerPreparedStatement.toString()过程中针对Decimal类型,添加了引号,在跟踪了execute发送给mysql的数据包都是使用了writeLenString进行处理,不过看了下针对int,long,float类型也没有明确的类型标识,估计是在mysql server端进行类型转换处理,刚好类型转换又有些bug,就触发了上面的问题。

 

发送数据包的格式代码:ServerPreparedStatement.storeBinding()

解决

去掉上面的两项配置定义,使用mysql的client-side模式进行处理,由客户端拼装好sql,直接到mysql上进行执行。针对client-side的batch的一些优化处理,可以参见PreparedStatement.executePreparedBatchAsMultiStatement方法

考虑

目前mysql driver默认的都是基于client-side模式进行处理,可参见mysql driver changelog: http://dev.mysql.com/doc/refman/5.1/en/cj-news-5-0-5.html 

5.0.5的版本里提到: 

1.Due to a number of issues with the use of server-side prepared statements, Connector/J 5.0.5 has disabled their use by default. The disabling of server-side prepared statements does not affect the operation of the connector in any way  

client-side和server-side的性能差别:

1. server-side需要和服务端的2次网络交互,一次进行prepare statement的构建过程,一次发送具体的bind数据。服务端可以缓存对应的prepare statement内容

2. client-side只需要一次和服务端的网络交互,但每次的sql都需要在服务端进行parse

 

所以针对server-side模式,如果不在jdbc客户端层面开启prepare statement cache,性能还不如client-side模式。而dbcp开启prepare statement cache,可能又会引发另外的问题: statement异常关闭。 在之前的dbcp测试过程中遇到过,后来就关闭了dbcp的prepare statement cache. 

时间: 2016-03-30

mysql number类型引号问题的相关文章

MySQL数值类型在binlog中需要注意的细节(r12笔记第69天)

    MySQL里的数值类型分得很细,光整型数据就有多种数据类型.tinyint,smallint,mediumint,int(integer),还有范围最大的bigint,它们对应的数值范围也大大不同,大体来说就是下面的数值范围,从有符号数和无符号数来区别对待. 类型名称 有符号数(signed) 无符号数(Unsigned) tinyint -129~127 0~255 smallint -32768~32767 0~65535 mediumint -8388608~8388607 0~1

ORACLE NUMBER类型详解

1>.NUMBER类型细讲:Oracle number datatype 语法:NUMBER[(precision [, scale])]简称:precision --> p      scale     --> s NUMBER(p, s)范围: 1 <= p <=38, -84 <= s <= 127保存数据范围:-1.0e-130 <= number value < 1.0e+126   保存在机器内部的范围: 1 ~ 22 bytes 有效为:

MYSQL列类型参考

mysql|参考    本附录介绍MySQL提供的每种列类型.关于利用每种类型的详细说明请参阅第2 章.除非另有说明,否则所列出的类型早在MySQL3.21.0 中就已经有了.    按下列约定给出类型名说明:    方括号( [ ]) 可选信息.    M 最大显示宽度.除非另有说明,否则M 应该是一个1到255 之间的整数.    D 有小数部分的类型的小数位数.D 为一个0 到30 之间的整数.D 应该小于等于M - 2.否则,M 的值将调整为D + 2.    在ODBC 术语中,M 和

移植-oracle timestamp(6) 对应mysql什么类型?

问题描述 oracle timestamp(6) 对应mysql什么类型? 1C 请教大家一个问题,关于数据库移植方面的内容.oracle到mysql移植在oracle中存在timestamp(6)类型,可以保存6位秒信息.那么在mysql有什么类型可以替换timestamp(6)呢?谢谢大家! 解决方案 mysql也有时间戳,参考http://database.51cto.com/art/200905/124240.htm 解决方案二: 没有直接对应的,可以转换一下http://databas

ORACLE NUMBER类型Scale为0引发的问题

今天遇到了一个很有意思的NUMBER类型Scale引发的问题,我用一个简单的测试用例来展示一下这个案例.假如有个TEST的表,有个字段类型为NUMBER,我插入下面两条数据 CREATE TABLE TEST (      Category VARCHAR(12),      QTY  NUMBER )   INSERT INTO TEST SELECT 'M', 12 FROM DUAL UNION ALL SELECT 'C', 0.99999999999999999 FROM DUAL;

mysql char类型主键做查询、更新条件时遇见怪事了,求大神解答

问题描述 mysql char类型主键做查询.更新条件时遇见怪事了,求大神解答 mysql> desc card_info; +-------------+-----------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------+------+-----+-------------------+-------+ |

ORACLE 中NUMBER类型默认的精度和Scale问题

在ORACLE数据库中,NUMBER(P,S)是最常见的数字类型,可以存放数据范围为10^-130~10^126(不包含此值),需要1~22字节(BYTE)不等的存储空间.P 是Precison的英文缩写,即精度缩写,表示有效数字的位数,最多不能超过38个有效数字.S是Scale的英文缩写,表示从小数点到最低有效数字的位数,它为负数时,表示从最大有效数字到小数点的位数.有时候,我们在创建表的时候,NUMBER往往没有指定P,S的值,那么默认情况下,NUMBER的P.S的值分别是多少呢?相信这个问

mysql-Linux 下 Mysql byte类型的问题

问题描述 Linux 下 Mysql byte类型的问题 最近遇到一个问题,数据库是Linux版的Mysql数据库, 在访问数据库做查询操作时, select substr(xxx,5,7) as time form xxx 返回来的结果集time变成了Byte类型..这是怎么回事,是MySql设置的问题吗?求解答 解决方案 byte类型的问题 解决方案二: http://www.cnblogs.com/zdz8207/p/3765073.html 应该是字符串

excel表内的时间导入到mysql datetime类型的数据是0

问题描述 excel表内的时间导入到mysql datetime类型的数据是0 excel表内的时间导入到mysql datetime类型的数据是0000-00-00 00:00:00,该怎么处理才好 工具是navicat for mysql 解决方案 你先在excel里把时间格式调整一下吧. 或者你先把excel里的数据导入到 sqlserver里, 毕竟他们是一家母司的东西, 兼容性比较强一点. 导入到sqlserver里了, 再导到mysql就不再是什么难事.