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'

存储过程函数,代码如下:

1. DELIMITER$$
3. DROPFUNCTIONIFEXISTS`fnObjectTagGetObjectTags`$$
4. CREATEDEFINER=`root`@`%`FUNCTION`fnObjectTagGetObjectTags`(_objectTypeint,_objectIdbigint)RETURNSvarchar(2048)CHARSETutf8
5. BEGIN
7. DECLARE_outObjectTagsVARCHAR(2048);
9. SET_outObjectTags=
10. (
11. SELECT(CAST(GROUP_CONCAT(CONCAT(tagId,'|',tagName)separator'~')ASCHAR(10000)CHARACTERSETutf8))ASobjectTagList
12. FROM
13. (
15. SELECTtagId,tagName
16. FROMobjectTag
17. INNERJOINtag
18. ONtagId=objectTagTagId
19. WHEREobjectTagObjectType=_objectType
20. ANDobjectTagObjectId=_objectId
21. ANDobjectTagIsDisabled=0
22. ANDobjectTagIsActive=1
23. ANDtagIsDisabled=0
24. ANDtagIsActive=1
25. )assubQuery
26. );
28. RETURN_outObjectTags;
30. END$$
32. DELIMITER;

使用过程,代码如下:

1. SELECTfnObjectTagGetObjectTags(3,album.albumId)
2. FROMalbum
3. WHEREfnObjectTagGetObjectTags(3,album.albumId)ISNOTNULL
4. ANDalbumIsDisabled=0
5. ANDalbumIsActive=1
7. TryanexplicitcastofjustthetagIdwww.phpfensi.comasacharacterbeforetheconcatenation,sinceyoumaybemixingbinaryandnon-binarystrings.Like
9. SELECT(CAST(GROUP_CONCAT(CONCAT(CAST(tagIdASCHAR),'|',tagName)separator'~')ASCHAR(10000)CHARACTERSETutf8))