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?
Aucun commentaire:
Enregistrer un commentaire