MySQL UPPER() and LOWER() not working with utf-8 characters
I have a search function, I want it to be case insensitive, including characters like éüò etc.
So, i transform the input to uppercase before querying the database. But MySQL doesn't convert the accented characters right.
SELECT * FROM items WHERE UPPER(descri开发者_StackOverflowption) = $input
I have MySQL 5.1.32, i have tried different collations but none seem to work right. Same with LOWER().
CREATE TABLE `items` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`description` text CHARACTER SET utf8 COLLATE utf8_bin,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=187 DEFAULT CHARSET=utf8"
The description field contains 'hellö'. Mysql converts it to 'HELLö'. I want 'HELLÖ'.
This works for me:
CREATE TABLE items (id INT NOT NULL, name VARCHAR(100) COLLATE UTF8_GENERAL_CI) ENGINE=InnoDB;
INSERT
INTO items
VALUES (1, 'Eyjafjallajökull');
SELECT *
FROM items
WHERE name = 'EYJAFJALLAJOKULL';
--
1 Eyjafjallajökull
SELECT UPPER('Eyjafjallajökull')
FROM items;
--
EYJAFJALLAJÖKULL
精彩评论