MySQL truncates concatenated result of a GROUP_CONCAT function

Viewed 27275

I've created a view which uses GROUP_CONCAT to concatenate results from a query on products column with data type of 'varchar(7) utf8_general_ci' in a column named concat_products.

The problem is that MySQL truncates value of "concat_products" column. phpMyAdmin says the data type of "concat_products" column is varchar(341) utf8_bin

Table products:

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

The "concat_products_vw" view:

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` 

According to MySQL manual:

Values in VARCHAR columns are variable-length strings
Length can be specified as a value from 1 to 255 before MySQL 4.0.2 and 0 to 255 as of MySQL 4.0.2.


EDIT

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

  1. Why MySQL specifies more than 255 characters for varchar "concat_products" column? (solved!)

  2. Why uf8_bin instead of utf8_general_ci?

  3. Is it possible to change the data type of a column in a view for example in my case to text for "concat_products" column?

  4. If not what can I do to prevent MySQL from truncating "concat_products" column?

2 Answers
Related