MYSQL:如何定义或获取LONG字符串变量 [英] MYSQL: How to define or get LONG string 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;

样本

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

它适用于...与小表一起使用，我可以将此@cols用于多种用途.但是，它停止使用大型表(大量字段).我注意到它在1024个字符处中断.它忽略LIMIT.是否有办法获得超过1024个字符的变量，或者解决此问题的方法?

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);

问题的根源是功能GROUP_CONCAT .该文档说GROUP_CONCAT的group_concat_max_len受max_allowed_packet限制: http://dev.mysql.com /doc/refman/5.0/en/group-by-functions.html#function_group-concat

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％发送给@ echo_me，50％发送给@barmar.它必须一起完成，否则将无法正常工作.

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

完整代码:

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;

这篇关于MYSQL:如何定义或获取LONG字符串变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！