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屋!