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.
Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535.
Why MySQL specifies more than 255 characters for varchar
"concat_products" column? (solved!)
Why uf8_bin
instead of utf8_general_ci
?
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?
If not what can I do to prevent MySQL from truncating "concat_products" column?
The difference here is while CONCAT is used to combine values across columns, GROUP_CONCAT gives you the capability to combine values across rows. It's also important to note that both GROUP_CONCAT and CONCAT can be combined to return desired results.
GROUP_CONCAT is a function which concatenates/merges the data from multiple rows into one field. It is a GROUP BY function which returns a string if the group contains at least 1 non-null value, if it does not, it returns a Null value.
Show activity on this post. I'm using GROUP_CONCAT() in a MySQL query to convert multiple rows into a single string. However, the maximum length of the result of this function is 1024 characters.
As I already wrote in an earlier comment, the MySQL manual says:
Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535.
So the problem is not with the data type of the field.
The MySQL manual also says:
The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer: SET [GLOBAL | SESSION] group_concat_max_len = val;
Your options for changing the value of group_concat_max_len are:
--group_concat_max_len=your_value_here
group_concat_max_len=your_value_here
SET GLOBAL group_concat_max_len=your_value_here;
SET SESSION group_concat_max_len=your_value_here;
Documentation: SET, Server System Variables: group_concat_max_len
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With