时间戳

首页 > 解决方案 > Django 和 mariadb 无法正确使用德语变音符号与 istartswith 一起使用

django - Django 和 mariadb 无法正确使用德语变音符号与 istartswith 一起使用

问题描述

以下查询应仅返回以“Ö”（德语变音符号）开头的所有城市。

letter = 'Ö'
City.objects.filter(name__istartswith=letter)

但它返回以 O 和 Ö 开头的城市。

我使用 django 1.11 和 mariadb。我已经将该表上的 COLLATE 设置为 utf8_bin 但这并没有改变 django 中的行为。

这是简化的 SQL 查询

SELECT `cities_city`.`name` FROM `cities_city` WHERE `cities_city`.`name` LIKE "Ö%";

这里是 SHOW CREATE TABLE 输出：

SHOW CREATE TABLE `cities_city`
    -> ;
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| cities_city | CREATE TABLE `cities_city` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) CHARACTER SET utf8 NOT NULL,
  `slug` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `name_std` varchar(200) CHARACTER SET utf8 NOT NULL,
  `location` point NOT NULL,
  `population` int(11) NOT NULL,
  `elevation` int(11) DEFAULT NULL,
  `kind` varchar(10) CHARACTER SET utf8 NOT NULL,
  `timezone` varchar(40) CHARACTER SET utf8 NOT NULL,
  `country_id` int(11) NOT NULL,
  `region_id` int(11) DEFAULT NULL,
  `subregion_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `cities_city_country_id_2f07e352_uniq` (`country_id`,`region_id`,`subregion_id`,`id`,`name`),
  KEY `cities_city_b068931c` (`name`),
  KEY `cities_city_16c3f481` (`name_std`),
  KEY `cities_city_region_id_0227cdac_fk_cities_region_id` (`region_id`),
  KEY `cities_city_subregion_id_9fbab97d_fk_cities_subregion_id` (`subregion_id`),
  CONSTRAINT `cities_city_country_id_779ae117_fk_cities_country_id` FOREIGN KEY (`country_id`) REFERENCES `cities_country` (`id`),
  CONSTRAINT `cities_city_region_id_0227cdac_fk_cities_region_id` FOREIGN KEY (`region_id`) REFERENCES `cities_region` (`id`),
  CONSTRAINT `cities_city_subregion_id_9fbab97d_fk_cities_subregion_id` FOREIGN KEY (`subregion_id`) REFERENCES `cities_subregion` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11468436 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

标签： djangomariadb

解决方案

问题是一个微妙的问题。

线索在这里：

SELECT * FROM information_schema.`COLUMNS` WHERE table_name = 'cities_city';

说明...

`name` varchar(200) CHARACTER SET utf8 NOT NULL,

是COLLATE utf8_general_ci因为这是 utf8 的默认排序规则。

此表默认：

) ENGINE=InnoDB AUTO_INCREMENT=11468436 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

给utf8_bin任何新添加的行。

也许您做了明显ALTER TABLE的更改为 _bin？反而：

ALTER TABLE cities_city
    CONVERT TO CHARACTER SET utf8
               COLLATE       utf8_bin;

这将进入每个字符串列并进行更改。请注意，排序规则更改时必须重建索引（等）。

推荐阅读