MYSQL截断了错误的INTEGER值错误

I am running a query and getting the mysql error 1292: "Truncated incorrect INTEGER value" It is a warning and my select works fine, but I would still like to clear up the warnings nonetheless.

我正在运行查询并获取mysql错误1292:“截断不正确的INTEGER值”这是一个警告,我的选择工作正常,但我仍然想清除警告。

I can confirm that the warning occurs everytime the function finds tags linked to the object. When no tags are found, the warning does not occur. So if 50 out of 1000 objects have tags, I will get 50 warnings, like this:

我可以确认每次函数找到链接到对象的标记时都会发出警告。如果未找到任何标签,则不会发出警告。因此,如果1000个对象中有50个有标签,我将收到50个警告,如下所示:

Truncated incorrect INTEGER value: '1|Blondes'

The database function used is:

使用的数据库函数是:

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 ;

And the calling query is simply:

调用查询只是:

SELECT fnObjectTagGetObjectTags(3, album.albumId)
FROM album
WHERE fnObjectTagGetObjectTags(3, album.albumId) IS NOT NULL
AND albumIsDisabled = 0
AND albumIsActive = 1

I just can't figure out why it is doing this. Anyone see anything odd? I am running 5.5.13 Thanks

我只是想不通为什么这样做。有人看到奇怪吗?我正在运行5.5.13谢谢

1 个解决方案

#1

Try an explicit cast of just the tagId as a character before the concatenation, since you may be mixing binary and non-binary strings. Like

在连接之前尝试将tagId显式转换为字符,因为您可能混合使用二进制和非二进制字符串。喜欢

SELECT (CAST(GROUP_CONCAT(CONCAT(CAST(tagId AS CHAR), '|', tagName) separator '~') AS CHAR(10000) CHARACTER SET utf8))

SELECT(CAST(GROUP_CONCAT(CONCAT(CAST(tagId AS CHAR),'|',tagName)separator'〜')AS CHAR(10000)CHARACTER SET utf8))

#1