Виртуальный столбец MySQL и подстановочный знак
Я пытался выполнить вторичное индексирование MySQL, ссылаясь на Документацию MySQL, и произошла странная вещь.
- Во-первых, я создал таблицу с небольшой модификацией по примеру в документе.
create table jemp(
c JSON,
g VARCHAR(20) GENERATED ALWAYS AS (c->"$.name"),
INDEX i (g)
)
- Во-вторых, я вставил значения для примера в документе
INSERT INTO jemp (c) VALUES
('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),
('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');
- А затем я попытался выполнить нечеткий поиск с помощью «нравится» и «подстановочный знак». Это не работает, потому что индекс не поддерживает префикс
%, но может получить результат.
select c->"$.name" as name from jemp where g like "%F%"
- Вот что странно, я убрал префикс
%, и индекс заработал. Однако никаких результатов я не получил. По моему плохому пониманию MySQL, это должно работать.
select c->"$.name" as name from jemp where g like "F%"
Я был бы очень признателен, если бы кто-нибудь мог помочь мне с этим.
@BillKarwin Спасибо за ваш комментарий. Впрочем, мой вопрос не об этом. Индекс работает, а запрос нет.
Ответы 1
Чтобы ваш запрос работал, вам нужен сгенерированный столбец, который извлекает имя в виде текста, а не JSON. То есть используйте ->> вместо ->:
g VARCHAR(20) GENERATED ALWAYS AS (c ->> '$.name')
Затем: индекс может помочь для обоих следующих условий:
where g like 'F%'
where g = 'F'
Решит ли MySQL использовать его или нет, это уже другая история; в основном базы данных оценивают, будет ли использование индекса быстрее, чем полное сканирование. Если он считает, что условие будет соответствовать большому количеству строк, он, вероятно, выберет полное сканирование.
Обратите внимание, что я постоянно использую одинарные кавычки для строковых литералов; хотя MySQL допускает иное, это то, что определяет стандарт SQL. В некоторых других базах данных идентификаторы обозначаются двойными кавычками (это также соответствует стандарту).
Ага! Оно работает! Но почему? В приведенном выше примере я также создал виртуальный столбец для c -> '$.name'. Я не могу сказать разницу между ними. Я отмечу ваш ответ как лучший, и если вы сможете объяснить его подробнее, я буду очень признателен.
Спасибо за ваше дальнейшее разъяснение. Я посмотрю документ и погуглю о разнице между -> и ->> в этом случае. Большое спасибо!
@Ray: -> возвращает значение JSON, поэтому оно заключено в двойные кавычки, например '"Fred"'. Если вам нужно строковое значение, вам нужно вывести его из кавычек, используя ->>, чтобы получить 'Fred'. Попробуйте в запросе и выяснить самостоятельно.
У вас есть только две тестовые строки данных в таблице? Возможно, вы не заставите MySQL использовать индекс, пока не сохраните достаточно строк, чтобы это стоило того. Также MySQL может не использовать индекс, если шаблон, который вы ищете, соответствует> 20% строк. Оптимизатор полагает, что дешевле просто просмотреть таблицу, чем заморачиваться с загрузкой индекса. Между прочим, ни одна из этих возможностей не относится к использованию виртуальных столбцов. Они случаются, когда вы используете индексы для обычных столбцов.