A-A+

mysql更新数据提醒Truncated incorrect INTEGER value:错误

2017年10月18日编程技术暂无评论

在mysql中更新数据时出现了Truncated incorrect INTEGER value错误,但仔细查看语句没什么问题,于时又运行还是有错,然后直接利用phpmyadmin生成一条才发现php用多了直接把习惯放在了mysql中.

在网上都找不到什么解决的方法,但是百度到一个个人博客后找到了解决的方法,报错的sql语句,代码如下:

update xx user set qq_password = 'xx_id' & photo = 'img' WHERE email = 'emailx'

然后这个是正确的执行语句,代码如下:

update xx user set qq_password = 'xx_id',photo = 'img' WHERE email = 'emailx'

把&改为,逗号就好了,后来看一老外使用存储过得也碰到这样的问题.

Truncated incorrect INTEGER value:'1|Blondes'

存储过程函数,代码如下:

DELIMITER $$
DROP FUNCTION IF EXISTS `fnObjectTagGetObjectTags` $$
CREATE DEFINER=`root`@`%` FUNCTION `fnObjectTagGetObjectTags`(_objectType int, _objectId bigint) RETURNS varchar(2048) CHARSET utf8
BEGIN
DECLARE _outObjectTags VARCHAR(2048);
SET _outObjectTags =
(
SELECT (CAST(GROUP_CONCAT(CONCAT(tagId, '|', tagName) separator '~') AS CHAR(10000) CHARACTER SET utf8)) AS objectTagList
FROM
(
SELECT tagId, tagName
FROM objectTag
INNER JOIN tag
ON tagId = objectTagTagId
WHERE objectTagObjectType = _objectType
AND objectTagObjectId = _objectId
AND objectTagIsDisabled = 0
AND objectTagIsActive = 1
AND tagIsDisabled = 0
AND tagIsActive = 1
) as subQuery
);
RETURN _outObjectTags;
END $$
DELIMITER ;

使用过程,代码如下:

SELECT fnObjectTagGetObjectTags(3, album.albumId)
FROM album
WHERE fnObjectTagGetObjectTags(3, album.albumId) IS NOT NULL
AND albumIsDisabled = 0
AND albumIsActive = 1
Try an explicit cast of just the tagId www.xiaohuboke.com as a character before the concatenation, since you may be mixing binary and non-binary strings. Like
SELECT (CAST(GROUP_CONCAT(CONCAT(CAST(tagId AS CHAR), '|', tagName) separator '~') AS CHAR(10000) CHARACTER SET utf8))

小虎博客