MYSQL截断了不正确的INTEGER值错误 [英] MYSQL Truncated incorrect INTEGER value error

问题描述

我正在运行查询并获取mysql错误1292:被截断了不正确的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.

我可以确认每次函数找到链接到该对象的标签时都会发生警告.如果找不到标签，则不会发生警告.因此，如果1000个对象中有50个具有标签，我将收到50个警告，如下所示:

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:

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

我只是不知道为什么要这么做.有人看到什么奇怪的东西吗? 我正在运行5.5.13 谢谢

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

推荐答案

在连接之前，请尝试仅将tagId显式转换为字符，因为您可能会混合使用二进制和非二进制字符串.像

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

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

这篇关于MYSQL截断了不正确的INTEGER值错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！