GROUP_CONCAT mysql

一直在oracle、mysql、hadoop之间切换思维。今天刚好在做数据库对比的时候，发现hadoop的元数据库（mysql），使用group_concat把该表的列所有信息，全部拼成一条字符串。发现列的个数不对，所以怀疑有字符限制。所以google了一把，果然有限制。 为了博客不重复写， 我这边是写如何在java程序中设置全局参数，放开限制。 不用重启mysql，也不需要修改mysql配置。

查询元数据sql如下：

SELECT CONCAT(hb.name,'_',hb.tbl_name,'_',hb.tbl_type,'_',hb.nameEn) hb_name 
				,hb.tbl_name  
				FROM (  
				 SELECT 
		           dt.name 
		         ,dt.tbl_name  
		         ,dt.tbl_type  
		         ,GROUP_CONCAT(dt.nameEn ORDER BY dt.integer_idx SEPARATOR ',') nameEn 
		          FROM ( 
		        SELECT  
		          d.name,  
		          t.tbl_name, 
		          t.tbl_type, 
		          CONCAT(c.`COLUMN_NAME`,'  ',c.`TYPE_NAME`)   nameEn, 
		          c.`TYPE_NAME` dataType, 
		          c.integer_idx       
		        FROM 
		          TBLS t, 
		          SDS s, 
		          COLUMNS_V2 c , 
		          DBS d  
		        WHERE s.`SD_ID` = t.`SD_ID`  
		          AND c.`CD_ID` = s.`CD_ID`  
		          AND d.db_id = t.db_id 
		          AND d.name IN ('prestat') 
		          AND c.cd_id=88825 
		          ORDER BY  d.name, t.tbl_type,t.tbl_name, c.integer_idx 
		          ) dt 
		          GROUP BY  
		          dt.name 
		         ,dt.tbl_name 
		         ,dt.tbl_type 
		         ) hb;

解释一下：

GROUP_CONCAT(dt.nameEn ORDER BY dt.integer_idx SEPARATOR ',') nameEn 

这句会丢掉部分数据，默认是1024，超过会被截断。

所以我在对比数据库 表结构时。明显2个表列都不一致，居然说相当。 我的程序也太水了。

最后发现是sql出了问题。

 

看看我的库：

mysql> use hive;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>  show variables like 'group_concat_max_len';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 4     |
+----------------------+-------+
1 row in set (0.02 sec)

mysql> 

直接在java操作，jdbc设置全局参数为51200

		// 先把所有的create存入map中
		Map<String, String> mapcreate = new HashMap<String, String>();
		// 以表名为key ，建表语句为value 存入mapcreate
		ResultSet rscreate = ds_1.executeSQL(DbSql.produceSrcCreateSql(srcdb));
		ds_1.executeSQL("SET GLOBAL group_concat_max_len=51200");
		
		while (rscreate.next()) {
			mapcreate.put(rscreate.getString(2), rscreate.getString(1));
		}
		System.out.println("map Cached Complete");
		ds_1.close();
		// 导出数据
		List<Map<String, Object>> data = new ArrayList<Map<String, Object>>();

注意：一定要重新登录会话，再可以见设置成功。

mysql>  show variables like 'group_concat_max_len';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 4     |
+----------------------+-------+
1 row in set (0.02 sec)

mysql>  show variables like 'group_concat_max_len';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 4     |
+----------------------+-------+
1 row in set (0.00 sec)

mysql> exit;
Bye
[root@master01 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 314476
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  show variables like 'group_concat_max_len';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 51200 |
+----------------------+-------+
1 row in set (0.00 sec)

mysql> 

mysql不需重启。