mysql中varchar的长度问题

[cube_chen]

VARCHAR列中的值为可变长字符串。长度可以指定为0到65,535之间的值。(VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节）。

参考手册上是这么说的,但是我定义的varchar(1000) str,当我传入的str的长度超过255的时候,就报错,各位大哥给解决下,谢谢!

[WWWWA]

测试了一下，可以啊，贴你的SP代码

[cube_chen]

CREATE DEFINER=`root`@`%` PROCEDURE `phonesp`(
	starttime varchar(255),
	endtime varchar(255),
	str varchar(1000),
	startPage int,
	maxPerPage int
)
BEGIN
	DECLARE cnt int; 
	declare h int default 0; 
	declare tbl_name varchar(20);
    declare tbl_name1 varchar(20);
	declare dt varchar(20);
	declare ho varchar(2);
	declare flag int default -1;
	declare readCount int default 0; 
	declare cur_row_count int default 0; 
	declare row_count int default 0;
	declare startRowNum int;
	declare findStartRow boolean default false;
	
	set startRowNum = (startPage - 1) * maxPerPage + 1;
	set cnt=timestampdiff(hour,starttime,endtime);
	while h <= cnt do
	if readCount < maxPerPage then
	    set dt = date_format(starttime,'%Y%m%d%H');
	    set tbl_name=concat('vq',dt,'0000');
        set tbl_name1=concat('bicc',dt,'0000');		
		set @test = concat("select count(`TABLE_NAME`) into @flag from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`='VQM' and `TABLE_NAME`='",tbl_name1,"';");
		prepare s0 from @test;
		execute s0;
		set flag = @flag;
		deallocate prepare s0;
		if flag>0 then
		    set @x = concat('SELECT count(*) into @cur_row_count FROM ',tbl_name1,' bicc,',tbl_name,' vq where bicc.local_sdp_ip=vq.src_ip or bicc.local_sdp_ip=vq.dst_ip and bicc.remote_sdp_ip=vq.dst_ip or bicc.remote_sdp_ip=vq.src_ip and bicc.local_sdp_port=vq.src_port or bicc.local_sdp_port=vq.dst_port and bicc.remote_sdp_port=vq.dst_port or bicc.remote_sdp_ip=vq.src_port',str);    
		    prepare s1 from @x;
		    execute s1;
		    deallocate prepare s1;
		    set cur_row_count = @cur_row_count;
		    set row_count = row_count + cur_row_count;
			if row_count - startRowNum >= 0 then
				if !findStartRow then
					if row_count - startRowNum >= maxPerPage  then
					 	set @x = concat('SELECT vq.*,bicc.calling_number,bicc.called_number FROM ',tbl_name1,' bicc,',tbl_name,' vq where bicc.local_sdp_ip=vq.src_ip or bicc.local_sdp_ip=vq.dst_ip and bicc.remote_sdp_ip=vq.dst_ip or bicc.remote_sdp_ip=vq.src_ip and bicc.local_sdp_port=vq.src_port or bicc.local_sdp_port=vq.dst_port and bicc.remote_sdp_port=vq.dst_port or bicc.remote_sdp_ip=vq.src_port',str,' limit ',cur_row_count - (row_count - startRowNum) - 1,',',maxPerPage);    					
						prepare s1 from @x;
						execute s1;
						deallocate prepare s1;
						set readCount = maxPerPage;
					else
					 	set @x = concat('SELECT vq.*,bicc.calling_number,bicc.called_number FROM ',tbl_name1,' bicc,',tbl_name,' vq where bicc.local_sdp_ip=vq.src_ip or bicc.local_sdp_ip=vq.dst_ip and bicc.remote_sdp_ip=vq.dst_ip or bicc.remote_sdp_ip=vq.src_ip and bicc.local_sdp_port=vq.src_port or bicc.local_sdp_port=vq.dst_port and bicc.remote_sdp_port=vq.dst_port or bicc.remote_sdp_ip=vq.src_port',str,' limit ',(cur_row_count - (row_count - startRowNum) - 1),',',row_count - startRowNum);    
					end if;
					set findStartRow = true;
				else
				 	set @x = concat('SELECT vq.*,bicc.calling_number,bicc.called_number FROM ',tbl_name1,' bicc,',tbl_name,' vq where bicc.local_sdp_ip=vq.src_ip or bicc.local_sdp_ip=vq.dst_ip and bicc.remote_sdp_ip=vq.dst_ip or bicc.remote_sdp_ip=vq.src_ip and bicc.local_sdp_port=vq.src_port or bicc.local_sdp_port=vq.dst_port and bicc.remote_sdp_port=vq.dst_port or bicc.remote_sdp_ip=vq.src_port',str,' limit ',0,',',maxPerPage - readCount);
					prepare s1 from @x;
					execute s1;
					deallocate prepare s1;
					set readCount = readCount + (cur_row_count - (maxPerPage - readCount - cur_row_count));
				end if;
			end if;
	    end if;
	end if; 
	    set h=h+1;
	    set starttime=timestampadd(hour,1,starttime);
	end while;
end;

[WWWWA]

怎么调用的？

[liangCK]

concat('SELECT vq.*,bicc.calling_number,bicc.called_number FROM ',tbl_name1,' ....

前面一大串.都一样的.就是后面的不一样..写了3次..不累呀..
在前面..赋好值..然后在后面判断一下..再连接字符串.

[cube_chen]

在java程序中调用的,当传入的str的长度太长的时候(超过255)的时候程序报下面的错误:
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column 'str' at row 1

[cube_chen]

[Quote=引用 4 楼 liangCK 的回复:]
concat('SELECT vq.*,bicc.calling_number,bicc.called_number FROM ',tbl_name1,' ....

前面一大串.都一样的.就是后面的不一样..写了3次..不累呀..
在前面..赋好值..然后在后面判断一下..再连接字符串.
[/Quote]

呵呵,谢谢,我继续改!

[wwwwb]

这应该是JAVA的限制吧？在JAVA中，字符串最长可以达到1000？

[ACMAIN_CHM]

MySQL应该没有这种限制。应该是你 jdbc上的设置，检查一下。

[cube_chen]

呵呵,还真没注意过java中String对象的长度,网上搜了下,说是2^31-1字节,约等于2G.所以应该不是java的限制!

[cube_chen]

我把str的类型改为text就可以了!
但是这个varchar(n),n的大小问题还是没搞清.