Mysql обрезает конкатенированный результат функции GROUP_CONCAT

Я создал представление, которое использует GROUP_CONCAT для конкатенации результатов из запроса в столбце продуктов с типом данных 'varchar(7) utf8_general_ci' в столбце с именем concat_products. Проблема заключается в том, что mysql обрезает значение столбца concat_products. phpMyAdmin говорит, что тип данных столбца concat_products varchar(341) utf8_bin

настольные продукты:

CREATE TABLE `products`(
`productId` tinyint(2) unsigned NOT NULL AUTO_INCREMENT, 
`product` varchar(7) COLLATE utf8_general_ci NOT NULL, 
`price` mediumint(5) unsigned NOT NULL, 
PRIMARY KEY (`productId`)) 
ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

concat_products_vw Просмотр:

CREATE VIEW concat_products_vw AS
SELECT
  `userId`,
    GROUP_CONCAT(CONCAT_WS('_', `product`, `productId`, `price`) 
        ORDER BY `productId` ASC SEPARATOR '*') AS concat_products
FROM
  `users`
LEFT JOIN `products` 
ON `users`.`accountBalance` >= `product`.`price`
GROUP BY `productId`

согласно руководству mysql

Значения в столбцах VARCHAR являются строками переменной длины
Длина может быть указана как значение от 1 до 255 до MySQL 4.0.2 и от 0 до 255 с MySQL 4.0.2.

изменить:

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535.

  • Почему mysql указывает более 255 символов для столбца varchar concat_products? (разрешено!)

  • Почему uf8_bin вместо utf8_general_ci?

  • Можно ли изменить тип данных столбца в представлении, например, в моем случае, в текст для столбца concat_products?

  • Если это не то, что я могу сделать, чтобы предотвратить деактивацию mysql столбца concat_products?

Ответы

Ответ 1

Как я уже писал в предыдущем комментарии, в руководстве MySQL говорится:

Значения в столбцах VARCHAR представляют собой строки переменной длины. Длина может указывается как значение от 0 до 65535.

Таким образом, проблема связана не с типом данных поля.

В руководстве MySQL также говорится:

Результат усекается до максимальной длины, заданной system_concat_max_len, которая имеет значение по умолчанию 1024. Значение может быть установлено выше, хотя эффективная максимальная длина возвращаемого значения ограничена значением max_allowed_packet. Синтаксис для изменения значения group_concat_max_len во время выполнения выглядит следующим образом: val unsigned integer: SET [GLOBAL | СЕССИЯ] group_concat_max_len = val;

Ваши параметры для изменения значения group_concat_max_len:

  • изменение значения при запуске MySQL путем добавления этого к команде:
    --group_concat_max_len=your_value_here
  • добавив эту строку в ваш конфигурационный файл MySQL (mysql.ini): group_concat_max_len=your_value_here
  • запуск этой команды после запуска MySQL:
    SET GLOBAL group_concat_max_len=your_value_here;
  • запуск этой команды после открытия соединения MySQL:
    SET SESSION group_concat_max_len=your_value_here;

Документация: SET, Системные переменные сервера: group_concat_max_len

Ответ 2

Как упоминал Джоселин, размер результата GROUP_CONCAT() ограничен group_concat_max_len, однако есть дополнительное взаимодействие с ORDER BY, что приводит к дальнейшему усечению до 1/3 от group_concat_max_len. Например, см. этот связанный ответ.

Значение по умолчанию для group_concat_max_len равно 1024, а 1024/3 = 341, вероятно, объясняет, почему тип concat_products отображается как varchar(341) в исходном примере. Если вы должны удалить предложение GROUP BY productId, concat_products должен отображаться как varchar(1024).

Я не нашел этого взаимодействия между GROUP_CONCAT() и ORDER BY, упомянутым в Руководстве по MySQL, но он затрагивает, по крайней мере, MySQL Server 5.1.

Ответ 3

Чтобы использовать ответы Joyce и JoshS, если вы используете mysql.connector и pandas.read_sql(), сначала передайте

cursor = cnx.cursor() query = "SET SESSION group_concat_max_len = 1000000"

перед запуском pandas.read_sql() для запроса данных, где cnx - это соединение, которое вы создали для mysql.connector. Также может быть так, что использование SET SESSION работает легче, не требуя более высокого уровня привилегий по сравнению с SET GLOBAL.