mysql更新數據提醒Truncated incorrect INTEGER value: 錯誤

在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.111cn.net 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))