教程集 www.jiaochengji.com
教程集 >  数据库  >  mysql  >  正文 mysql更新数据提醒Truncated incorrect INTEGER value: 错误

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

发布时间:2023-04-30   编辑:jiaochengji.com
教程集为您提供mysql更新数据提醒Truncated incorrect INTEGER value: 错误等资源,欢迎您收藏本站,我们将为您提供最新的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))

您可能感兴趣的文章:
有关mysql不能插入中文的问题
mysql提示Incorrect string value: ‘\xF0\x9F\x9B\xB3, …’ for column ‘option_value’解决办法
详解MySQL数据库的集合类型SET的DDL变更方法
mysql问题:Too many connections的解决方法
ftp连接提示Incorrect password or username
防止mysql表被清空的方法详解
【哈希密码】PHP比md5更安全的加密方式
csv导入mysql数据库的实例代码
MySQL常见错误代码解析
有关Mysql数据截断问题的处理方法

[关闭]
~ ~