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

在网上都找不到什么解决的方法。但是百度到一个个人博客后找到了解决的方法

报错的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))

时间： 2024-08-07 17:09:03

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

mysql更新数据提醒Truncated incorrect INTEGER value: 错误的相关文章

mysql保存数据提示1366 Incorrect string value: ‘\xF0\x9F\x98\x8A\xF0\x9F…’ 解决

Mysql错误1366 - Incorrect integer value怎么解决？

Mysql错误1366 - Incorrect integer value解决方法_Mysql

ORACLE导出文本到MYSQL 报错 Incorrect integer value: ''

php 解决MySQL插入数据出现 Incorrect string value: &amp;#39;\xF0\x9F\x92\x8BTi...&amp;#39;错误

mysql的数据修改后实时更新到elasticsearch的问题

MySQL入门插入、删除和更新数据sql语句

MySQL删除/更新数据时报1175错误

MySQL 随机查询数据与随机更新数据实现代码_Mysql