mysql的decimal长度之坑------Out of range value for column xxx

        踩了个坑, 来看看:

CREATE TABLE x (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cost DECIMAL(2, 1) NOT NULL
);

        然后:

mysql> INSERT INTO x(cost)VALUES(1.3);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO x(cost)VALUES(10.3);
ERROR 1264 (22003): Out of range value for column 'cost' at row 1
mysql> 

       注意, DECIMAL(2, 1)的格式, 和C语言中printf控制长度的格式类似。

       2是总长度, 额, 呵呵哒。

       

       没什么好说的。

       不多说。

 

 

发布了2203 篇原创文章 · 获赞 4516 · 访问量 1961万+
展开阅读全文

查询语句报 Out of range value for column

02-12

各位好,遇到一个问题需要向大家请教,我的环境是MySql 驱动包是mysql-connector-java-5.1.30.jar(试过很多版本都是一样),查询一条语句报 Out of range value for column,百度谷歌只发现插入更新报这个错误的资料,所以向大家请教,希望不吝赐教。 语句如下: SELECT CONCAT( date_format(dcm.DPRODUCTIONDATE, '%Y'), '年度' ) AS productionDate, date_format(dcm.DPRODUCTIONDATE, '%Y') AS shortDate, sum(COALESCE(dcm.NCOSTTIME, 0)) AS nCostTime, sum( COALESCE (dcm.NSTANDARDTIME, 0) ) AS standardTime2, CASE WHEN (1) = '0' THEN COALESCE ( sum(dcm.NNORMALSHUTDOWNTIME), 0 ) ELSE COALESCE ( sum(dcm.nNSDirectTimeQty), 0 ) END normalShutTime, CASE WHEN (1) = '0' THEN COALESCE ( sum(dcm.NABNORMALSHUTDOWNTIME), 0 ) ELSE COALESCE ( sum(dcm.nASDirectTimeQty), 0 ) END abnormalShutTime, CASE WHEN (1) = '0' THEN COALESCE (sum(dcm.NCOSTTIME), 0) ELSE COALESCE (sum(dcm.nStandardTime), 0) END standardTime, CASE WHEN (1) = '0' THEN COALESCE (sum(dcm.NATTENDANCETIME), 0) ELSE sum( COALESCE (dcm.NDIRECTTIME, 0) + COALESCE (dcm.nOTDirectTimeQty, 0) + COALESCE ( dcm.nDirectSupportTimeQty, 0 ) + COALESCE ( dcm.nOTDirectSupportTimeQty, 0 ) ) END attendanceTime, CASE WHEN (1) = '0' THEN fncalcefficiency ( sum( COALESCE (dcm.nAttendanceTime, 0) ), sum( COALESCE (dcm.NSTANDARDTIME, 0) ), 0, 0 ) ELSE fncalcefficiency ( sum( COALESCE (dcm.NDIRECTTIME, 0) + COALESCE ( dcm.nDirectSupportTimeQty, 0 ) + COALESCE (dcm.nOTDirectTimeQty, 0) + COALESCE ( dcm.nOTDirectSupportTimeQty, 0 ) ), sum( COALESCE (dcm.NSTANDARDTIME, 0) ), 0, 0 ) END generalRate, CASE WHEN (1) = '0' THEN fncalcefficiency ( sum( COALESCE (dcm.nAttendanceTime, 0) ), sum(COALESCE(dcm.NCOSTTIME, 0)), sum( COALESCE (dcm.NNORMALSHUTDOWNTIME, 0) ), 0 ) ELSE fncalcefficiency ( sum( COALESCE (dcm.NDIRECTTIME, 0) + COALESCE ( dcm.nDirectSupportTimeQty, 0 ) + COALESCE (dcm.nOTDirectTimeQty, 0) + COALESCE ( dcm.nOTDirectSupportTimeQty, 0 ) ), sum(COALESCE(dcm.NCOSTTIME, 0)), sum( COALESCE (dcm.nNSDirectTimeQty, 0) ), 0 ) END processRate, CASE WHEN (1) = '0' THEN fncalcefficiency ( sum( COALESCE (dcm.nAttendanceTime, 0) ), sum(COALESCE(dcm.NCOSTTIME, 0)), sum( COALESCE (dcm.NNORMALSHUTDOWNTIME, 0) ), sum( COALESCE ( dcm.NABNORMALSHUTDOWNTIME, 0 ) ) ) ELSE fncalcefficiency ( sum( COALESCE (dcm.NDIRECTTIME, 0) + COALESCE ( dcm.nDirectSupportTimeQty, 0 ) + COALESCE (dcm.nOTDirectTimeQty, 0) + COALESCE ( dcm.nOTDirectSupportTimeQty, 0 ) ), sum(COALESCE(dcm.NCOSTTIME, 0)), sum( COALESCE (dcm.nNSDirectTimeQty, 0) ), sum( COALESCE (dcm.nASDirectTimeQty, 0) ) ) END workRate FROM ec_XLOrganCostAndOutput dcm INNER JOIN ec_SysOrganization org ON ( org.id = 'efcb6a64-ac7e-4da8-a959-92904e9b3318' AND dcm.SLINKSERIALNOX LIKE CONCAT(org.SLINKSERIALNO, '%') ) WHERE org.istatus = '0' AND dcm.DPRODUCTIONDATE >= str_to_date('2016-03-01', '%Y-%m-%d') AND dcm.DPRODUCTIONDATE <= str_to_date('2016-03-31', '%Y-%m-%d') GROUP BY date_format(dcm.DPRODUCTIONDATE, '%Y') ORDER BY date_format(dcm.DPRODUCTIONDATE, '%Y') 错误信息如下: Exception in thread "main" com.mysql.jdbc.MysqlDataTruncation: Data truncation: Out of range value for column 'AttendanceTime' at row 695324 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4230) at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2188) at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:2044) at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:3538) at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:489) at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3240) at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2411) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2834) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2838) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2212) at test.ConnectService.main(ConnectService.java:232) 根据错误信息,说超出了,但是很奇怪的是,我把这条语句精剪,只查询2个字段又没错误: SELECT CONCAT( date_format(dcm.DPRODUCTIONDATE, '%Y'), '年度' ) AS productionDate, CASE WHEN (1) = '0' THEN COALESCE (sum(dcm.NATTENDANCETIME), 0) ELSE sum( COALESCE (dcm.NDIRECTTIME, 0) + COALESCE (dcm.nOTDirectTimeQty, 0) + COALESCE ( dcm.nDirectSupportTimeQty, 0 ) + COALESCE ( dcm.nOTDirectSupportTimeQty, 0 ) ) END attendanceTime FROM ec_XLOrganCostAndOutput dcm INNER JOIN ec_SysOrganization org ON ( org.id = 'efcb6a64-ac7e-4da8-a959-92904e9b3318' AND dcm.SLINKSERIALNOX LIKE CONCAT(org.SLINKSERIALNO, '%') ) WHERE org.istatus = '0' AND dcm.DPRODUCTIONDATE >= str_to_date('2016-03-01', '%Y-%m-%d') AND dcm.DPRODUCTIONDATE <= str_to_date('2016-03-31', '%Y-%m-%d') GROUP BY date_format(dcm.DPRODUCTIONDATE, '%Y') ORDER BY date_format(dcm.DPRODUCTIONDATE, '%Y') 所以很是迷惑,不知道错误到底是什么? 问答

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 技术工厂 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览