MySQL GROUP_CONCAT function: common mistakes
The function returns a single string, not a list of values. It means you cannot use the result of the function for IN operator e.g., within a subquery.
For example, the function returns the result of values: , and as the ‘1,2,3’ string.
If you supply this result to the operator, the query is not working. Therefore, the query may not return any result. For example, the following query will not work as desired.
Because the operator accepts a list of values e.g., (1,2,3) not a string that consists of a list of values (‘1,2,3’). As a result, the following query will not work as expected.
Because the function is an aggregate function, to sort the values, you must use the clause inside the function, not in the in the SELECT statement.
The following example demonstrates the incorrect usage of the clause in the context of using the function:
The clause returns one string value so the clause does not take any effect in this statement.
MySQL function applications
There are many cases where you can apply the function to produce useful results. The following list is some common examples of using the function.
- Make a comma-separated user’s roles such as ‘admin, author, editor’.
- Produce the comma-separated user’s hobbies e.g., ‘design, programming, reading’.
- Create tags for blog posts, articles or products e.g., ‘mysql, mysql aggregate function, mysql tutorial’.
In this tutorial, you have learned how to use the MySQL function to concatenate non- values of a group of strings into a single string.
MySQL GROUP_CONCAT() function examples
Let’s take a look at the table in the sample database.
To get all countries where customers locate as a comma-separated string, you use the function as follows:
However, some customers located in the same country. To remove the duplicate country’s names, you add the clause as the following query:
It is more readable if the country’s names are in ascending order. To sort the country’s name before concatenating, you use the clause as follows:
To change the default separator of the returned string from a comma (,) to a semi-colon (;), you use the clause as the following query:
Great! now you know how the function works. Let’s put it in a practical example.
Each customer has one or more sale representatives. In other words, each sales employee is in charge of one or more customers. To find out who in charge of which customers, you use the inner join clause as follows:
Now, we can group the result set by the employee number and concatenate all employees that are being in charge of the employee by using the function as follows:
The result set is much easier to read.
Using MySQL with function example
Sometimes, the function can be combined with the CONCAT_WS function to make a result of query more useful.
For example, to make a list of semicolon-separated values of customers:
- First, you concatenate the last name and first name of each customer’s contact using the function. The result is the contact’s full name.
- Then, you use the function to make the list.
The following query makes a list of semicolon-separated values of customers.
Note that function concatenates string values in different rows while the or function concatenates two or more string values in different columns.
Устаревший способ группировки
Оператор GROUP BY является великолепным инструментом для выборки связанных данных. Но он не подходит для точной сортировки данных.
Представим, что мы являемся владельцами пункта проката фильмов и желаем вознаградить тех клиентов, которые брали много ужастиков. Для этого нам нужно узнать, какие фильмы брал в прокате каждый клиент. Один из способов сделать это — переместить инструкцию GROUP BY SELECT во вложенный запрос, который возвращает идентификаторы пользователей, отвечающие всем требованиям. Затем можно ограничить результаты внешнего запроса теми клиентами, чьи идентификаторы являются частью внутреннего результирующего набора.
Ниже приводится код SQL, который выполнит эту работу без MySQL GROUP CONCAT SEPARATOR:
SELECT CONCAT(CU.last_name, ', ', CU.first_name) AS customer,
A.phone,
F.title,
date(R.rental_date) AS rental_date
FROM sakila.rental R
LEFT JOIN sakila.inventory I ON R.inventory_id = I.inventory_id
LEFT JOIN sakila.film F ON I.film_id = F.film_id
LEFT JOIN sakila.film_category FC on F.film_id = FC.film_id
LEFT JOIN sakila.category C ON FC.category_id = C.category_id
LEFT JOIN sakila.customer CU ON R.customer_id = CU.customer_id
LEFT JOIN sakila.address A ON CU.address_id = A.address_id
WHERE CU.customer_id in
(SELECT CU.customer_id
FROM rental R
LEFT JOIN sakila.customer CU ON R.customer_id = CU.customer_id
LEFT JOIN sakila.inventory I ON R.inventory_id = I.inventory_id
LEFT JOIN sakila.film F ON I.film_id = F.film_id
LEFT JOIN sakila.film_category FC on F.film_id = FC.film_id
LEFT JOIN sakila.category C ON FC.category_id = C.category_id
WHERE C.name = "Horror"
GROUP BY CU.customer_id
HAVING COUNT(CU.customer_id) >= 3)
AND C.name = "Horror"
ORDER BY customer, title, rental_date DESC;
Получаем трех первых клиентов с названиями фильмов, взятых напрокат, и датами:
customer phone title rental_date ---------------------------------------------------------------- ADAM, NATHANIEL 111177206479 ANALYZE HOOSIERS 2005-08-19 ADAM, NATHANIEL 111177206479 FREDDY STORM 2005-08-22 ADAM, NATHANIEL 111177206479 STRANGERS GRAFFITI 2005-08-23 ANDREW, JOSE 961370847344 EGYPT TENENBAUMS 2005-07-31 ANDREW, JOSE 961370847344 FIDELITY DEVIL 2005-05-30 ANDREW, JOSE 961370847344 HIGH ENCINO 2005-07-07 ANDREW, JOSE 961370847344 LOLA AGENT 2005-08-02 AQUINO, OSCAR 474047727727 AFFAIR PREJUDICE 2005-07-28 AQUINO, OSCAR 474047727727 DRUMS DYNAMITE 2005-06-20 AQUINO, OSCAR 474047727727 EGYPT TENENBAUMS 2005-07-28 AQUINO, OSCAR 474047727727 STREETCAR INTENTIONS 2005-08-01 и т. д…
Работает, даже несмотря на то, что внутренние и внешние операторы SQL WHERE повторяются. Но не это главное — приложение, которое получает результаты запросов, должно отслеживать имена клиентов, чтобы знать, когда перейти к следующему. Я проделывал это много раз, и в результатах всегда присутствовала путаница.
Introduction to MySQL GROUP_CONCAT() function
The MySQL function is an aggregate function that concatenates strings from a group into a single string with various options.
The following shows the syntax of the function:
The following example demonstrates how the function works.
The clause allows you to eliminate duplicate values in the group before concatenating them.
The clause allows you to sort the values in ascending or descending order before concatenating. By default, it sorts the values in ascending order. If you want to sort the values in the descending order, you need to specify explicitly the option.
The specifies a literal value inserted between values in the group. If you do not specify a separator, the function uses a comma (,) as the default separator.
The function ignores values. It returns if there was no matching row found or all arguments are values.
The function returns a binary or non-binary string, which depends on the arguments. by default, the maximum length of the return string is 1024. In case you need more than this, you can extend the maximum length by setting the system variable at or level.
Как узнать время жизни сессии
Перед настройкой, стоит посмотреть текущее состояние. Есть несколько методов это сделать:
1. На сервере командой php
Вводим:
php -i | grep session
Получаем список параметров, имеющих отношение к сессиям. Нас интересуют:
- session.cookie_lifetime => 0 => 0
- session.gc_maxlifetime => 1440 => 1440
Данные значения — значение по умолчанию. cookie_lifetime => 0 говорит о действии файлов куки до закрытия браузера, если задать этому параметру определенное значение, сессия будет прерываться при активном сеансе, поэтому лучше ее оставлять в значении ноль.
2. C помощью php-функции ini_get
<?php
$maxlifetime = ini_get(«session.gc_maxlifetime»);
$cookielifetime = ini_get(«session.cookie_lifetime»);
echo $maxlifetime;
echo $cookielifetime;
?>
Как автоматически продлевать сессии
Если сессия выдается на определенный период и заканчивается в определенное время, это может привести к прерыванию активного сеанса пользователя. Гораздо удобнее, если время действия сессии будет автоматически продлеваться, если посетитель обновляет страницу. Для этого существует параметр cookie_lifetime, который во всех примерах выше мы задавали в значении 0.
Если мы зададим значение cookie_lifetime 86400, то через 24 часа сессия прервется. Это не всегда удобно.
Если есть необходимость в контроле и прерывании сессии, можно воспользоваться php-функцией session_destroy().
MySQL group_concat set group_concat_max_len
http-equiv=»Content-Type» content=»text/html;charset=UTF-8″>style=»clear:both;»>
GROUP_CONCATЭта функция используется для соединения нескольких строк в строку. При объединении строк возникает проблема с длиной сплайсинга. Максимальная длина сплайсинга mysql по умолчанию -1024 Байт, потому что 1024 байта будет недостаточно, поэтому иногда необходимо изменить в соответствии с ситуацией, следующим образом.
1. Просмотр текущегоmysql group_concat_max_len
войтиmysqlСтатус, введите:show variables like ‘group_concat_max_len’;
Если вы не изменили его, вы получите следующий результат
2. Изменитьmysql group_concat_max_len
а) если перезапуск не удобноmysql допустимыйmysqlСтатус устанавливается командами, такими как:
-
SET GLOBAL group_concat_max_len = 102400;
-
SET SESSION group_concat_max_len = 102400;
Проверьте по пути 1.
Примечание. Этот метод считывает файл конфигурации для сброса после перезапуска mysql, что приводит к неправильной настройке, поэтому рекомендуется по-прежнему изменять файл конфигурации.
б), измените файл конфигурации:my.ini
вСледующая новая конфигурация:group_concat_max_len = 102400
Перезагрузите, вы можете проверить это способом 1.
В частности, иногда мы не знаем, сколько байтов необходимо для удовлетворения спроса. В этом случае мы можем рассмотреть возможность не устанавливать максимальный байт (то есть использовать максимальное количество байтов).）Т.е. установить в конфигурационном файлеgroup_concat_max_len=-1
В этом случае результаты можно проверить способом 1 следующим образом:
Замечания:4294967295 = 2^32 — 1
Интеллектуальная рекомендация
1. Для реальных сигналов (для понимания): A (ω) является соотношением амплитуды выходного сигнала и амплитуды входного сигнала, называемого частотой амплитуды. Φ (ω) — это разница межд…
Один. вести Многие люди задавали некоторые вопросы о создании проекта Flex + LCDS (FDS) в сообщениях и группах. Из-за операции ее трудно четко объяснить, поэтому я написал простой учебник (я обещал эт…
package com.example.phonehttp; import android.os.Bundle; import android.os.Handler; import android.app.Activity; import android.widget.ScrollView; import android.widget.TextView; public class MainActi…
![[switch] проблема с усечением функции mysql group_concat - русские блоги](https://fuzeservers.ru/wp-content/uploads/7/8/7/78765f6bddb2418fc19fe8634565cfab.jpeg)
Он предназначен для реализации подкласса того же родительского класса с родительским классом. Полиморфизм Один и тот же ссылочный тип использует разные экземпляры для выполнения разных операций; Идея …
тема: Объедините два упорядоченных слоя в новый заказанный список и возврат. Новый список состоит из всех узлов двух связанных списков, данных сплавным. Пример: Анализ: два связанных списка состоит в …
Вам также может понравиться
D. Самая ценная строка Пример ввода 2 2 aa aaa 2 b c Образец вывода aaa c На самом деле, будучи задетым этим вопросом, вы должны быть осторожны. После инвертирования строки, если две строки имеют один…
Given a 2D integer matrix M representing the gray scale of an image, you need to design a smoother to make the gray scale of each cell becomes the average gray scale (rounding down) of all the 8 surro…
calc () может быть очень незнакомым для всех, и трудно поверить, что calc () является частью CSS. Поскольку он выглядит как функция, почему он появляется в CSS, поскольку это функция? Этот момент такж…
Основываясь на дереве регрессии, сформированном CART, а также на предварительной и последующей обрезке дерева, код выглядит следующим образом:…
Откат Обновление в режиме онлайн с версии Centos (CentOS Linux версии 7.3.1611 (Core) до CentOS Linux версии 7.5.1804 (Core)) # ошибка соединения yum-ssh после обновления yexpected key exchange group …
Настройка через файл .htaccess
Данный файл позволяет веб-мастеру управлять некоторыми настройками веб-сервера. Для его редактирования нужен доступ к файлам сайта. Способ не сработает, если в качестве обработчика PHP используется не Apache, а, например, NGINX + PHP-FPM. Хотя, тут тоже есть способ (о нем будет ниже).
В файл .htaccess вносим следующее:
php_value session.gc_maxlifetime 86400
php_value session.cookie_lifetime 0
* как можно заметить, параметры те же, что при настройки через php.ini.
Как говорилось выше, метод не сработает, если не используется Apache. Однако настройку можно выполнить на сервере (опять же, у нас должен быть соответствующий доступ).
Открываем файл настройки веб-сервера, например, в php-fpm:
vi /etc/php-fpm.d/www.conf
и редактируем/добавляем:
php_value = 86400
php_value = 0
После перезапускаем сервис:
systemctl restart php-fpm || service php-fpm restart
SQL Справочник
SQL Ключевые слова
ADD
ADD CONSTRAINT
ALTER
ALTER COLUMN
ALTER TABLE
ALL
AND
ANY
AS
ASC
BACKUP DATABASE
BETWEEN
CASE
CHECK
COLUMN
CONSTRAINT
CREATE
CREATE DATABASE
CREATE INDEX
CREATE OR REPLACE VIEW
CREATE TABLE
CREATE PROCEDURE
CREATE UNIQUE INDEX
CREATE VIEW
DATABASE
DEFAULT
DELETE
DESC
DISTINCT
DROP
DROP COLUMN
DROP CONSTRAINT
DROP DATABASE
DROP DEFAULT
DROP INDEX
DROP TABLE
DROP VIEW
EXEC
EXISTS
FOREIGN KEY
FROM
FULL OUTER JOIN
GROUP BY
HAVING
IN
INDEX
INNER JOIN
INSERT INTO
INSERT INTO SELECT
IS NULL
IS NOT NULL
JOIN
LEFT JOIN
LIKE
LIMIT
NOT
NOT NULL
OR
ORDER BY
OUTER JOIN
PRIMARY KEY
PROCEDURE
RIGHT JOIN
ROWNUM
SELECT
SELECT DISTINCT
SELECT INTO
SELECT TOP
SET
TABLE
TOP
TRUNCATE TABLE
UNION
UNION ALL
UNIQUE
UPDATE
VALUES
VIEW
WHERE
MySQL Функции
Функции строк
ASCII
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT
CONCAT_WS
FIELD
FIND_IN_SET
FORMAT
INSERT
INSTR
LCASE
LEFT
LENGTH
LOCATE
LOWER
LPAD
LTRIM
MID
POSITION
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SPACE
STRCMP
SUBSTR
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UPPER
Функции чисел
ABS
ACOS
ASIN
ATAN
ATAN2
AVG
CEIL
CEILING
COS
COT
COUNT
DEGREES
DIV
EXP
FLOOR
GREATEST
LEAST
LN
LOG
LOG10
LOG2
MAX
MIN
MOD
PI
POW
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SUM
TAN
TRUNCATE
Функции дат
ADDDATE
ADDTIME
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE
DATEDIFF
DATE_ADD
DATE_FORMAT
DATE_SUB
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SECOND
SEC_TO_TIME
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME
TIME_FORMAT
TIME_TO_SEC
TIMEDIFF
TIMESTAMP
TO_DAYS
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK
Функции расширений
BIN
BINARY
CASE
CAST
COALESCE
CONNECTION_ID
CONV
CONVERT
CURRENT_USER
DATABASE
IF
IFNULL
ISNULL
LAST_INSERT_ID
NULLIF
SESSION_USER
SYSTEM_USER
USER
VERSION
SQL Server функции
Функции строк
ASCII
CHAR
CHARINDEX
CONCAT
Concat with +
CONCAT_WS
DATALENGTH
DIFFERENCE
FORMAT
LEFT
LEN
LOWER
LTRIM
NCHAR
PATINDEX
QUOTENAME
REPLACE
REPLICATE
REVERSE
RIGHT
RTRIM
SOUNDEX
SPACE
STR
STUFF
SUBSTRING
TRANSLATE
TRIM
UNICODE
UPPER
Функции чисел
ABS
ACOS
ASIN
ATAN
ATN2
AVG
CEILING
COUNT
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MAX
MIN
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SQUARE
SUM
TAN
Функции дат
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR
Функции расширений
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME
MS Access функции
Функции строк
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase
Функции чисел
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val
Функции дат
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year
Другие функции
CurrentUser
Environ
IsDate
IsNull
IsNumeric
SQL ОператорыSQL Типы данныхSQL Краткий справочник
Способ создания группированного списка с помощью функции GROUP_CONCAT
Функция MySQL GROUP CONCAT не является новой. Она объединяет все ненулевые значения из группы и возвращает их в виде строки с разделителями-запятыми. В сочетании с оператором GROUP BY она позволяет поместить сгруппированные данные в одну строку.
Перепишем наш код, применив функцию GROUP_CONCAT:
SELECT CONCAT(CU.last_name, ', ', CU.first_name) AS customer,
A.phone,
date(R.rental_date) AS rental_date,
GROUP_CONCAT(F.title) AS titles,
COUNT(*) AS rentals_count
FROM sakila.rental R
LEFT JOIN sakila.inventory I ON R.inventory_id = I.inventory_id
LEFT JOIN sakila.film F ON I.film_id = F.film_id
LEFT JOIN sakila.film_category FC on F.film_id = FC.film_id
LEFT JOIN sakila.category C ON FC.category_id = C.category_id
LEFT JOIN sakila.customer CU ON R.customer_id = CU.customer_id
LEFT JOIN sakila.address A ON CU.address_id = A.address_id
WHERE C.name = "Horror"
GROUP BY R.customer_id
HAVING rentals_count >= 3
ORDER BY customer, title, rental_date DESC;
Как видите, c помощью MySQL GROUP CONCAT решена проблема с лишними данными, поскольку больше не нужно отфильтровывать результаты.
Фильмы, взятые напрокат, перечислены в колонке «titles»:
customer phone rental_date titles rentals_count -------------------------------------------------------------------------------------------------------------------------------- ADAM, NATHANIEL 111177206479 2005-08-22 FREDDY STORM,ANALYZE HOOSIERS,STRANGERS GRAFFITI 3 ANDREW, JOSE 961370847344 2005-07-31 EGYPT TENENBAUMS,LOLA AGENT,FIDELITY DEVIL,HIGH ENCINO 4 AQUINO, OSCAR 474047727727 2005-07-28 EGYPT TENENBAUMS,AFFAIR PREJUDICE,STREETCAR INTENTIONS,DRUMS DYNAMITE 4 ARTIS, CARL 20064292617 2005-08-18 BOWFINGER GABLES,RULES HUMAN,YENTL IDAHO,FIDELITY DEVIL 4 BARBEE, CLAYTON 380077794770 2005-05-26 BEHAVIOR RUNAWAY,LOVE SUICIDES,SWARM GOLD 3 и т. д…
Кроме этого решена еще одна задача — отображение сгруппированных данных в одной строке. Это положительно сказывается на работе приложения, так как доступ к сгруппированным данным осуществляется с помощью одной операции.
Это довольно простой процесс с использованием функции разбиения строк MySQL GROUP CONCAT, который реализуется большинством языков программирования. Например, в PHP эта функция называется «explode». В качестве параметров функция принимает разделитель и строку, и возвращает данные в виде массива. Ниже приведен пример того, как можно получить названия фильмов (titles), используя упомянутый выше запрос:
//извлечение результирующего набора
$res=$mysqli--->query($select_statement);
//итерация по каждой строке
while ($row = $res->fetch_array(MYSQLI_ASSOC)) {
//эта инструкция разделяет строку titles
//запятыми в массиве
$titles_array = explode(',', $row);
//работа с массивом названий...
}
Еще одним преимуществом использования функции GROUP_CONCAT является то, что строковое значение можно применять как часть оператора IN:
$res_films = $mysqli->query("SELECT * FROM sakila.film WHERE title = IN ($titles_array)");
// работа с $res_films...
Настройка сессий на веб-сервере
Выполняется путем настройки файла php.ini. Данный способ удобен, если мы являемся администратором веб-сервера, а также если есть гарантия, что общая настройка сессий не повлияет на работоспособность всех веб-приложений, работающих на данном сервере.
Открываем на редактирование php.ini. Его расположение зависит от сборки Linux. Точный путь можно посмотреть командой:
php -i | grep php.ini
Теперь открываем сам файл:
vi /etc/php.ini
* в моем случае каманда php -i | grep php.ini вернула данный путь.
В некоторых системах, например, Ubuntu или Debian для каждой среды обработки кода создается свой php.ini файл, а также для каждой версии PHP. Например, по пути /etc/php/7.4/fpm/php.ini находится файл для php-fpm для PHP версии 7.4. Нам необходимо учитывать данный факт, чтобы настроить нужный файл.
И редактируем следующие параметры:
session.gc_maxlifetime = 86400
session.cookie_lifetime = 0
* где параметр gc_maxlifetime указывает на временя в секундах, после прошествии которого данные могут быть удалены; cookie_lifetime — время жизни файлов cookies; 86400 — 24 часа в секундах.
* если параметру gc_maxlifetime задать значение 0, действие сессий будет бесконечным. Это, как правило, не стоит делать — приведет к падению производительности и безопасности сервера.
После настройки параметров, необходимо перезагрузить сервер, являющийся интерпретатором PHP.
Если это apache:
systemctl restart apache2 || systemctl restart httpd
* в версиях Linux без systemd используем команду service apache2 restart или service httpd restart.
Если используем FastCGI (PHP-FPM).
а) для CentOS:
systemctl restart php-fpm
б) для Ubuntu или Debian:
systemctl restart php7.4-fpm
* где 7.4 — версия используемого PHP.
SQL Учебник
SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии
