在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$$
- DROPFUNCTIONIFEXISTS`fnObjectTagGetObjectTags`$$
- CREATEDEFINER=`root`@`%`FUNCTION`fnObjectTagGetObjectTags`(_objectTypeint,_objectIdbigint)RETURNSvarchar(2048)CHARSETutf8
- BEGIN
- DECLARE_outObjectTagsVARCHAR(2048);
- SET_outObjectTags=
- (
- SELECT(CAST(GROUP_CONCAT(CONCAT(tagId,'|',tagName)separator'~')ASCHAR(10000)CHARACTERSETutf8))ASobjectTagList
- FROM
- (
- SELECTtagId,tagName
- FROMobjectTag
- INNERJOINtag
- ONtagId=objectTagTagId
- WHEREobjectTagObjectType=_objectType
- ANDobjectTagObjectId=_objectId
- ANDobjectTagIsDisabled=0
- ANDobjectTagIsActive=1
- ANDtagIsDisabled=0
- ANDtagIsActive=1
- )assubQuery
- );
- RETURN_outObjectTags;
- END$$
- DELIMITER;
使用过程,代码如下:
- SELECTfnObjectTagGetObjectTags(3,album.albumId)
- FROMalbum
- WHEREfnObjectTagGetObjectTags(3,album.albumId)ISNOTNULL
- ANDalbumIsDisabled=0
- ANDalbumIsActive=1
- TryanexplicitcastofjustthetagIdwww.phpfensi.comasacharacterbeforetheconcatenation,sinceyoumaybemixingbinaryandnon-binarystrings.Like
- SELECT(CAST(GROUP_CONCAT(CONCAT(CAST(tagIdASCHAR),'|',tagName)separator'~')ASCHAR(10000)CHARACTERSETutf8))
