#mysql #sql
Вопрос:
У меня есть таблица с уникальным ограничением на 3 столбца:
ПРОБЛЕМА: Допустим, одна строка
col5 col7 col9
lemon orange apple
если я попытаюсь изменить ту же самую строку на (просто обновите до заглавной буквы):
col5 col7 col9
lemon Orange apple
Я получаю сообщение об ошибке Дублировать запись лимон-Апельсин-яблоко. В основном mysql сравнивает предлагаемую модификацию не только с другими строками таблицы, но и с самой измененной строкой (до модификации).
После некоторого устранения неполадок я полагаю, что это связано с ДЛИННЫМ текстом… но не уверен, как его решить…
вопрос
Есть ли способ сохранить регистр таблицы/столбцов без учета регистра, но позвольте mysql понять, что я просто изменяю строку, поэтому ей не нужно сравнивать значения строк с собой (измененная строка)?
ОБНОВЛЕНИЕ (более подробная информация)
Кто-нибудь может воспроизвести эту проблему?
Версия сервера: 10.4.17-MariaDB
После запуска ПОКАЗАТЬ СОЗДАТЬ ТАБЛИЦУ это результат:
CREATE TABLE `mytable`
( `post_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'my comment',
`col1` varchar(255) DEFAULT NULL COMMENT 'unique or NULL',
`col2` varchar(255) NOT NULL,
`col3` int(11) DEFAULT NULL,
`col4` int(11) DEFAULT NULL COMMENT 'CONSTRAIN: Accepts only NULL and post_ID',
`col5` longtext NOT NULL,
`col6` longtext NOT NULL,
`col7` longtext NOT NULL,
`col8` longtext NOT NULL,
`col9` longtext NOT NULL,
`col10` longtext NOT NULL,
PRIMARY KEY (`post_ID`), UNIQUE KEY `col1` (`col1`),
UNIQUE KEY `col5` (`col5`(255),`col7`(255),`col9`(255)) USING HASH COMMENT '!!!These columns together MUST be unique!!!',
KEY `key_1` (`col3`), KEY `key_2` (`col4`) )
ENGINE=InnoDB AUTO_INCREMENT=120 DEFAULT CHARSET=utf8mb4
Вставьте данные теста:
INSERT INTO `mytable`
(`post_ID`, `col1`, `col2`, `col3`, `col4`, `col5`, `col6`, `col7`, `col8`, `col9`, `col10`)
VALUES (NULL, NULL, '', NULL, NULL, 'apple', '', 'orange', '', 'apple', '');
Попробуйте обновить оранжевый до оранжевого (просто введя слово Оранжевый в поле phpMyAdmin):
UPDATE `mytable` SET `col7` = 'Orange' WHERE `mytable`.`post_ID` = 120
Ошибка:
#1062 - Duplicate entry 'apple-Orange-apple' for key 'col5'
ОПРОБОВАННЫЕ РЕШЕНИЯ
Я попытался изменить параметры сортировки на utf8mb4_bin или latin7_general_cs (как предлагалось в других вопросах) для сравнения с учетом регистра; это решает проблему изменения только заглавных букв, но это не работает для меня, потому что все поиски стали чувствительными к регистру (очевидно), и когда я ищу в таблице «оранжевый», он не найдет «Оранжевый».
Попытался также чередовать таблицу make utf8mb4_bin и столбцы utf8mb4_general_ci (и наоборот), но безуспешно. Те же проблемы. После некоторого устранения неполадок я полагаю, что это связано с ДЛИННЫМ текстом… но не уверен, как его решить…
Комментарии:
1. Вы используете сопоставление, не зависящее от регистра. Используйте параметры сортировки с
_csпостфиксом (или двоичным). Или укажите параметры сортировки в условных выражениях. Также помните, что в табличных данных используются параметры сортировки базы данных/таблицы/столбца, тогда как строковый литерал, указанный в тексте запроса, использует параметры сортировки соединений.2. MySQL не поддерживает массивы, поэтому я не понимаю названия.
3. @GordonLinoff Извините: я имел в виду «ограничение», а не массив!
4. @Akina Спасибо, но я не могу найти «utf8mb4_cs» в phpMyAdmin… Я могу найти cs только в латинских сопоставлениях. Использование latin2_general_cs или latin7_general_cs будет одинаковым? Что ты делаешь?
5. Сопоставление @Akina cs не работает 🙁 . Мне нужно, чтобы поиск не учитывал регистр, но мог изменять ОДНУ и ту же строку с «оранжевого» на оранжевый…
Ответ №1:
MySQL 8.0.13 поддерживает функциональные индексы. Таким образом, вы можете делать все, что хотите, используя уникальный индекс:
create unique index idx_t_col1_col2_col3 on t( (convert(col1 using binary)), (convert(col2 using binary)), (convert(col3 using binary)) )
В более старых версиях 8.0 вы можете имитировать это, создавая вычисляемые столбцы, а затем создавая индекс для этих столбцов.
Вот более простой пример, в котором используется только один столбец.
Комментарии:
1. Не совсем ясно, чего хочет операция. Это позволило бы дублировать столбцы, в которых они отличаются только в случае, но он, похоже, просто хочет иметь возможность обновлять их…что делает неясным, почему возникает ошибка
2. Я опубликовал более подробную информацию в своем ответе, не могли бы вы, ребята, взглянуть на это… Должно быть, я сделал что-то не так… Спасибо за вашу помощь!
Ответ №2:
[РЕШЕНО!!]
По счастливой случайности проблема решена!
Не знаю как, но это те шаги, которые я сделал:
- Следуя комментарию Гарра Годфри («…если они являются ДЛИННЫМ ТЕКСТОМ, mysql требует, чтобы в индексе была установлена ДЛИНА») Я добавил длину (1000) в индекс.
[на этом этапе все еще не работает, но полезно для следующих шагов]
- Грубая/принудительная перезагрузка сервера (на самом деле батарея разрядилась 😮 !!)
- в phpMyAdmin: Оптимизация таблицы (в надежде, что это была перестройка индекса)
ТА-ДААА! Теперь мы вернулись к делу : ) !
Спасибо всем за вашу поддержку!