mysql函数截断,Mysql截断GROUP_CONCAT函数的连接结果

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

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.

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?

解决方案

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:

changing the value at MySQL startup by appending this to the command:

--group_concat_max_len=your_value_here

adding this line in your MySQL configuration file (mysql.ini): group_concat_max_len=your_value_here

running this command after MySQL startup:

SET GLOBAL group_concat_max_len=your_value_here;

running this command after opening a MySQL connection:

SET SESSION group_concat_max_len=your_value_here;