mysql 字符串 变量_MYSQL：如何定义或获取LONG字符串变量

I am running the following query:

Variables

SET @src = 'Test';

SET @tgt = 'Test2';

SET @db = 'Test';

SET @pk = 'ID, MyPk, etc';

SELECT CONCAT( @pk, GROUP_CONCAT(CONCAT( ", ", COLUMN_NAME) SEPARATOR "") )

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = @db

AND TABLE_NAME = @src

INTO @cols;

Sample

SET @sql = CONCAT( 'INSERT INTO `', @tgt, '` (SELECT ', @cols, ' FROM `', @src, '`);' );

PREPARE stm FROM @sql;

EXECUTE stm;

It works...with small tables and I can use this @cols for multiple purposes. However, it stops working with large tables (Large amount of fields). I noticed it breaks at 1024 characters. It ignores LIMIT. Is there away to get a variable longer than 1024 characters or a way around this problem?

解决方案SET GLOBAL group_concat_max_len = (7*1024);

SET GLOBAL max_allowed_packet = (50*1024*1024);

The root of the problem was the function GROUP_CONCAT. the documentation says GROUP_CONCAT's group_concat_max_len IS LIMITED BY max_allowed_packet:

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

50% to @echo_me and 50% to @barmar. It has to be done together, otherwise it wont work.

Full code:

SET GLOBAL group_concat_max_len = (7*1024);

SET GLOBAL max_allowed_packet = (50*1024*1024);

SET @src = 'Test';

SET @tgt = 'Test2';

SET @db = 'Test';

SET @pk = 'ID, MyPk, etc';

SELECT CONCAT( @pk, GROUP_CONCAT(CONCAT( ", ", COLUMN_NAME) SEPARATOR "") )

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = @db

AND TABLE_NAME = @src

INTO @cols;