Mysql tronque le résultat concaténé d'une fonction de concaténation de groupe

J'ai créé une vue qui utilise GROUP_CONCAT pour concaténer les résultats d'une requête sur la colonne produits avec le type de données 'varchar(7) utf8_general_ci' dans une colonne nommée concat_products. Le problème est que mysql tronque la valeur de la colonne concat_products. phpMyAdmin indique que le type de données de la colonne concat_products est varchar(341) utf8_bin

Produits De Table: 

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 Vue: 

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`

Selon le manuel mysql

Les valeurs des colonnes VARCHAR sont des chaînes de longueur variable
La longueur peut être spécifié comme une valeur de 1 à 255 avant MySQL 4.0.2 et 0 à 255 à partir de MySQL 4.0.2.

Modifier:

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

  1. Pourquoi mysql spécifie plus de 255 caractères pour la colonne varchar concat_products?(résolu!)

  2. Pourquoi uf8_bin au lieu de utf8_general_ci?

  3. Est-il possible de changer le type de données d'une colonne dans une vue par exemple dans mon cas en texte pour la colonne concat_products?

  4. Si non, que puis-je faire pour empêcher mysql de tronquer la colonne concat_products?

35
demandé sur Brian Tompsett - 汤莱恩 2012-08-17 11:55:37

2 réponses

Comme je l'ai déjà écrit dans un commentaire précédent, le manuel MySQL dit:

Les valeurs des colonnes VARCHAR sont des chaînes de longueur variable. La longueur peut être spécifié comme une valeur comprise entre 0 et 65 535.

Donc, le problème n'est pas avec le type de données du champ.

Le manuel MySQL dit aussi:

Le résultat est tronqué à la longueur maximale donnée par group_concat_max_len, qui a une valeur par défaut de 1024. La valeur peut être définie plus haut, bien que la longueur maximale effective de la valeur de retour soit limitée par la valeur de max_allowed_packet. La syntaxe pour modifier la valeur de group_concat_max_len au moment de l'exécution est la suivante, où val est un entier non signé: Définir [GLOBAL | SESSION] group_concat_max_len = val;

Vos options pour changer la valeur de group_concat_max_len sont:

  1. Modification de la valeur au démarrage de MySQL en l'ajoutant au commande:
    --group_concat_max_len=your_value_here
  2. ajout de cette ligne dans votre fichier de configuration MySQL (mysql.ini): group_concat_max_len=your_value_here
  3. exécuter cette commande après le démarrage de MySQL:
    SET GLOBAL group_concat_max_len=your_value_here;
  4. exécuter cette commande après l'ouverture D'une connexion MySQL:
    SET SESSION group_concat_max_len=your_value_here;

Documentation: ensemble, Variables système du serveur: group_concat_max_len

64
répondu Jocelyn 2012-08-17 10:06:10

Comme Jocelyn l'a mentionné, la taille d'un résultat GROUP_CONCAT() est limitée par group_concat_max_len, cependant il y a une interaction supplémentaire avec ORDER BY qui entraîne une nouvelle troncature à 1/3 de group_concat_max_len. Pour un exemple, voir cette réponse.

La valeur par défaut pour group_concat_max_len est 1024, et 1024 / 3 = 341 explique probablement pourquoi le type de concat_products apparaît comme varchar(341) dans l'exemple original. Si vous deviez supprimer la clause GROUP BY productId, concat_products devrait apparaître comme varchar(1024).

Je n'ont pas trouvé cette interaction entre GROUP_CONCAT() et ORDER BY mentionné dans le manuel MySQL, mais il affecte au moins MySQL Server 5.1.

6
répondu JoshS 2017-05-23 12:25:29