开发者

MySQL UPPER() and LOWER() not working with utf-8 characters

开发者 https://www.devze.com 2023-01-24 22:50 出处:网络
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

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
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号