MYSQL函数group_concat的使用_MySQL-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

MYSQL函数group_concat的使用_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 01, 2016 pm 01:41 PM

mysql

bitsCN.com
MYSQL函数group_concat的使用今天对一批数据要迁移转换，查了下，有group_concat这个函数简单实现字段的列转行设置，过程记录如下一.测试数据准备mysql> use test;Database changedmysql> select * from t_kenyon;+------+| id |+------+| 1 || 123 || 789 || 345 || 78 |+------+5 rows in set (0.00 sec)二.使用经过 1.以默认的逗号作为分隔符mysql> select group_concat(id) from t_kenyon;+------------------+| group_concat(id) |+------------------+| 1,123,789,345,78 |+------------------+1 row in set (0.00 sec)2.对ID值进行排序后行转列mysql> select group_concat(id order by id) from t_kenyon;+------------------------------+| group_concat(id order by id) |+------------------------------+| 1,78,123,345,789 |+------------------------------+1 row in set (0.00 sec)3.使用其他分割符，如*和;等mysql> select group_concat(id separator '*') from t_kenyon;+--------------------------------+| group_concat(id separator '*') |+--------------------------------+| 1*123*789*345*78 |+--------------------------------+1 row in set (0.00 sec)
4.分隔符与排序结合起来用mysql> select group_concat(id order by id separator '_') from t_kenyon;+--------------------------------------------+| group_concat(id order by id separator '_') |+--------------------------------------------+| 1_78_123_345_789 |+--------------------------------------------+1 row in set (0.00 sec)5.对相同的值分组mysql> insert into t_kenyon values (78);Query OK, 1 row affected (0.00 sec) mysql> select group_concat(id) from t_kenyon group by id;+------------------+| group_concat(id) |+------------------+| 1 || 78,78 || 123 || 345 || 789 |+------------------+5 rows in set (0.00 sec)
三.参数设置与限制说明 1.查看服务器中设置 mysql> show variables like '%group_concat%';+----------------------+-------+| Variable_name | Value |+----------------------+-------+| group_concat_max_len | 1024 |+----------------------+-------+1 row in set (0.00 sec)以上设置的值说明当前是默认长度1KB 2.改变参数值方法一：修改配置文件中参数，新增 group_concat_max_len = 10240 方法二：在会话中实现，全局或当前session中 SET GLOBAL group_concat_max_len=10240; SET SESSION group_concat_max_len=10240; 作者 kenyon bitsCN.com

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

MySQL: BLOB and other no-sql storage, what are the differences?May 13, 2025 am 12:14 AM

MySQL'sBLOBissuitableforstoringbinarydatawithinarelationaldatabase,whileNoSQLoptionslikeMongoDB,Redis,andCassandraofferflexible,scalablesolutionsforunstructureddata.BLOBissimplerbutcanslowdownperformancewithlargedata;NoSQLprovidesbetterscalabilityand

MySQL Add User: Syntax, Options, and Security Best PracticesMay 13, 2025 am 12:12 AM

ToaddauserinMySQL,use:CREATEUSER'username'@'host'IDENTIFIEDBY'password';Here'showtodoitsecurely:1)Choosethehostcarefullytocontrolaccess.2)SetresourcelimitswithoptionslikeMAX_QUERIES_PER_HOUR.3)Usestrong,uniquepasswords.4)EnforceSSL/TLSconnectionswith

MySQL: How to avoid String Data Types common mistakes?May 13, 2025 am 12:09 AM

ToavoidcommonmistakeswithstringdatatypesinMySQL,understandstringtypenuances,choosetherighttype,andmanageencodingandcollationsettingseffectively.1)UseCHARforfixed-lengthstrings,VARCHARforvariable-length,andTEXT/BLOBforlargerdata.2)Setcorrectcharacters

MySQL: String Data Types and ENUMs?May 13, 2025 am 12:05 AM

MySQloffersechar, Varchar, text, Anddenumforstringdata.usecharforfixed-Lengthstrings, VarcharerForvariable-Length, text forlarger text, AndenumforenforcingdataAntegritywithaetofvalues.

MySQL BLOB: how to optimize BLOBs requestsMay 13, 2025 am 12:03 AM

Optimizing MySQLBLOB requests can be done through the following strategies: 1. Reduce the frequency of BLOB query, use independent requests or delay loading; 2. Select the appropriate BLOB type (such as TINYBLOB); 3. Separate the BLOB data into separate tables; 4. Compress the BLOB data at the application layer; 5. Index the BLOB metadata. These methods can effectively improve performance by combining monitoring, caching and data sharding in actual applications.

Adding Users to MySQL: The Complete TutorialMay 12, 2025 am 12:14 AM

Mastering the method of adding MySQL users is crucial for database administrators and developers because it ensures the security and access control of the database. 1) Create a new user using the CREATEUSER command, 2) Assign permissions through the GRANT command, 3) Use FLUSHPRIVILEGES to ensure permissions take effect, 4) Regularly audit and clean user accounts to maintain performance and security.

Mastering MySQL String Data Types: VARCHAR vs. TEXT vs. CHARMay 12, 2025 am 12:12 AM

ChooseCHARforfixed-lengthdata,VARCHARforvariable-lengthdata,andTEXTforlargetextfields.1)CHARisefficientforconsistent-lengthdatalikecodes.2)VARCHARsuitsvariable-lengthdatalikenames,balancingflexibilityandperformance.3)TEXTisidealforlargetextslikeartic

MySQL: String Data Types and Indexing: Best PracticesMay 12, 2025 am 12:11 AM

Best practices for handling string data types and indexes in MySQL include: 1) Selecting the appropriate string type, such as CHAR for fixed length, VARCHAR for variable length, and TEXT for large text; 2) Be cautious in indexing, avoid over-indexing, and create indexes for common queries; 3) Use prefix indexes and full-text indexes to optimize long string searches; 4) Regularly monitor and optimize indexes to keep indexes small and efficient. Through these methods, we can balance read and write performance and improve database efficiency.

See all articles