STRCMP(x, y) – функція порівнює рядки х і у, повертає:
– 1, якщо х < у; 0, якщо х і у співпадають; 1, якщо х> у; NULL, якщо один з аргументів NULL.
MATCH (<Список стовпців>) AGAINST (<Критерій пошуку>) – повнотекстовий пошук по заданим ключовим словам. Для стовпців має бути створений повнотекстовий індекс. MATCH для кожного рядка повертає величину релевантності (ступінь близькості рядка критерію пошуку). Якщо MATCH використовується в параметрі WHERE команди SELECT, то результатом запиту будуть рядки з відмінною від нуля релевантністю, впорядковані по зменшенню релевантності.
SELECT * FROM Products WHERE MATCH (description) AGAINST (‘президент');
Логічні оператори – AND, OR, NOT, XOR.
Оператори і функції, які базуються на порівнянні:
Якщо: a < b1 – функція повертає значення 0; bi < a < bi+1 – функція повертає номер i; якщо: a > bn – функція повертає значення n.
Приклади.
SELECT IFNULL('some value', 'some other value'); повертає 'some value' SELECT IFNULL (NULL, 'some other value'); повертає 'some other value' SELECT COALESCE (NULL, 'some other value'); повертає 'some other value' – аналог функції IFNULL SELECT COALESCE (NULL, 'some value', 'some other value'); повертає 'some value' SELECT COALESCE (NULL, NULL, NULL, NULL, 'first non-null value'); повертає 'first non-null value'
Арифметичні оператори
Алгебраїчні функції
TRUNCATE(12345.6789, 2) = 12345.67, TRUNCATE(12345.6789, -2) = 12300,
Псевдовипадкові числа
SELECT RAND()*(y-x)+x; на інтервалі: [x, y] SELECT FLOOR(RAND()*(y-x+1))+x; цілі від x до y включно
Тригонометричні функції
Функції дати і часу
SELECT ADDDATE('2019-05-01', 7) as nextweek, CURDATE() as today; Результат: 2019-04-26, 2019-04-19
Функції форматів для дати-час
Параметри формату:
%c – номер місяця (0-12)
%d – число місяця (00–31)
%H – години (00–23)
%h – години (01–12)
%i – хвилини (00–59)
%m – номер місяцю (00–12)
%r – час 12-год (HH:MM:SS AM, PM)
%S – секунди (00–59)
%T – час у 24-год (HH:MM:SS)
%w – номер дня тижня (0 – неділя, 1 – понеділок,…)
%Y – номер року у форматі YYYY
%y – номер року в форматі YY
DATE_FORMAT('2007-12-12 12:30:00', %e %M %Y г. %k годин %i хвилин') повертає значення 12 Грудня 2007 г. 12 годин 30 хвилин. TIME_FORMAT('<Час або дата і час>', '<Формат>')\\ STR_TO_DATE('<Рядок>', '<Формат>') – рядок перетворює у дату/час\\
SELECT ADDDATE("2017-06-15 09:34:21", INTERVAL -3 HOUR) new_date; SELECT DATE_ADD(CURDATE(), INTERVAL 7 day) 'через 7 днів'; SELECT sum(sal+comm) sum1, sum(sal+ifnull(comm,0)) sum2 FROM emp ; SELECT sum(sal+ifnull(comm,0)) FROM emp ; SELECT FLOOR(RAND() * 401) + 100 AS `100...500` ;
Формат | Опис |
---|---|
%M | Назва місяця |
%W | Назва дня тиждня (воскресенье…суббота) |
%D | День мiсяцz з суффіксом (0st, 1st, 2nd, 3rd и т.д.) |
%Y | Рік, 4 розряда |
%y | Рік, 2 розряда |
%d | День місяця, число (00..31) |
%e | День місяця, число (0..31) |
%m | Місяць, число (00..12) |
%c | Місяць, число (0..12) |
%b | Скорочена назва місяця |
%j | День року (001..366) |
%H | Година (00..23) |
%k | Година (0..23) |
%h | Година (01..12) |
%I | Година (01..12) |
%l | Година (1..12) |
%i | Хвилина (00..59) |
%r | Час, 12. формат (hh:mm:ss [AP]M) |
%T | Час, 24. формат (hh:mm:ss) |
%s | Секунди (00..59) |
%p | AM або PM |
%u | Тиждень (00..53),де понеділок вважається першим днем тиждня |
Символьні функції
При необхідності можна явно вказати кодування, що зіставляє коди символів.
CHAR (77, 121, 83, 81, 76); -- ‘MYSQL’ CHAR (53402,53632,53643,53435,53438,53426 USING utf8) - повертає значення Крилов. CHAR (138,224,235,171,174,162 USING cp866) - повертає значення Крилов.
FIELD ( '<Рядок-зразок>', '<Рядок 1>', '<Рядок 2>', …, '<Рядок N>'). Повертає порядковий номер рядка, що збігається із зразком, і 0, якщо жодна з рядків 1, 2, …, N не збігається зі зразком.
FIELD ( 'Петров', 'Іванов', 'Петров', 'Сидоров') - повертає значення 2.
CONCAT('<Рядок 1>','< Рядок 2>',…, '< Рядок N>'), Повертає результат об'єднання своїх аргументів.
CONCAT_WS ( 'Роздільник', '<Рядок 1>', '<Рядок 2>', …, '<Рядок N>'). Повертає результат об'єднання своїх аргументів, при цьому перший аргумент використовується як роздільник.
CONCAT_WS ( ',', 'Іванов', 'Петров', 'Сидоров') - повертає значення Іванов, Петров, Сидоров.
ELT (k, '<Рядок 1>', '<Рядок 2>', …, '<Рядок N>'). Повертає рядок з порядковим номером k.
ELT (2, 'Іванов', 'Петров', 'Сидоров') - повертає значення Петров.
REPEAT('< Рядок>', N) – рядок, в який <рядок> входить N разів, REVERSE('< Рядок >') – обернений порядок символів, SPACE( N) – рядок з N пробілів. INSERT('<Рядок>', N, k,'<Заміщаючий підрядок>') – повертає рядок, в якому рядок з позиції N і довжини k, замінено заданим підрядком
INSERT ( 'ТОВ "Кускус"', 6,3, 'Кіскіс') - замінює підрядок Кус рядка ТОВ «Кускус» підрядком Кіскіс і повертає значення ТОВ «Кіскіскус».
REPLACE('<Рядок>','<Підрядок-1, який заміщується>','<Заміщуючий підрядок-2>'),
Функція REPLACE () повертає <рядок>, в якому <Підрядок-1> замінено <Підрядком-2>.
REPLACE ( ‘Сьогодні дуже холодно’, ‘холодно’, ‘тепло’) - повертає значення ‘Сьогодні дуже тепло’.
LENGTH ( '<Рядок>'), (або OCTET_LENGTH ( '<Рядок>‘)). Повертають довжину рядка в байтах.
LENGTH ( 'Крилов') - повертає значення 6 при використанні однобайтового кодування і значення 12 при використанні кодування UTF-8.
BIT_LENGTH('<Рядок>') – повертає довжину рядка в бітах.
BIT_LENGTH ( 'Крилов') - повертає значення 48 при використанні однобайтового кодування і значення 96 при використанні кодування UTF-8.
FIND_IN_SET ( '<Рядок-зразок>', '<Рядок-контейнер>'). Функція отримує в якості аргументу рядок-зразок (цей рядок не повинна містити коми) і рядок-контейнер виду <Підрядок 1>, <Підрядок2>, …, <Підрядок N> і повертає порядковий номер підрядка, що збігається із зразком , інакше 0.
FIND_IN_SET ( 'Петров', 'Іванов, Петров, Сидоров') - повертає значення 2.
SUBSTR ( '<Рядок>', <Позиція> [, <Довжина>]), SUBSTRING ( '<Рядок>', <Позиція> [, <Довжина>]). Повертають підрядок, що починається з вказаної позиції. Можна вказати довжину одержуваного підрядка. Якщо номер позиції < 0, то відлік починається від кінця рядка.
SUBSTR ( 'Сім чудес світу', 6) повертає значення ‘чудес світу’; SUBSTR ( 'Сім чудес світу', 6,5) повертає значення ‘чудес’;
LEFT('<Рядок>', N) – лівий підрядок довжини N,
RIGHT('< Рядок>', N) – правий підрядок довжини N,
LOCATE('<Підрядок>','< Рядок>'[,<Позиція>]) – повертає позицію, з якої починається перше входження підрядка в рядок. Якщо рядок не містить цей підрядок, то повертає 0. Можна вказати позицію, з якої шукається входження підрядка.
LOCATE ( 'сто', 'Не май сто рублів, а май сто друзів „) - повертає значення 9,
а функція LOCATE (' сто ',' Не май сто рублів, а май сто друзів", 20) повертає значення 28.
LOWER('< Рядок >') ) – переводить до нижнього регістру, UPPER('< Рядок >') ) – переводить до верхнього регістру, LTRIM('< Рядок >') рядок, отриманий видаленням початкових пробілів, RTRIM('< Рядок >') повертає рядок, отриманий видаленням кінцевих пробілів, TRIM('< Рядок >') рядок, отриманий видалення початкових та кінцевих пробілів.
Функції COMPRESS() і UNCOMPRESS()
Операція зберігання довгого тексту в БД впливає на швидкодію системи и на вимоги до об'єму дискового простору. Довгий текст рекомендується стискати за допомогою функції COMPRESS(), а перед його використанням виконати обернене перетворення за допомогою функції UNCOMPRESS().
SELECT LENGTH(COMPRESS(REPEAT('A', 1000000))); Результат: ~1000
1 000 000 символів ‘A’ стискається в рядок розміром приблизно в 1000 позицій.
Символьні функції типу REGEXP_
Символьна функція REGEXP_INSTR()
SELECT REGEXP_INSTR('dog cat dog', 'dog'); -- 1 SELECT REGEXP_INSTR('dog cat dog', 'dog', 2); -- 9 SELECT REGEXP_INSTR('aa aaa aaaa', 'a{2}'); -- 1 SELECT REGEXP_INSTR('aa aaa aaaa', 'a{4}'); -- 8
Символьна функція REGEXP_REPLACE ()
SELECT REGEXP_REPLACE('a b c', 'b', 'X'); -- a X c
Символьна функція REGEXP_LIKE ()
SELECT REGEXP_LIKE ('abc', ‘ABC'); -- 1
REGEXP – операції з регулярними виразами
Деякі характеристики розширених регулярних виразів:
id | name |
---|---|
1 | Samsung Widescreen 1080p |
2 | Geforce GT 8800 |
3 | Mini Tv |
4 | Audio System |
5 | HTC PRO 2 |
Повнотекстовий пошук в таблицях типу MyISAM
MySQL підтримуєь методи повнотекстового пошуку (fulltext search), які дозволяють дуже швидко знаходити потрібні дані у текстах великого розміру.
Повнотекстовий індекс в MySQL — це індекс типу FULLTEXT.
Повнотекстові індекси можуть використовуватись з таблицями MyISAM (в MySQL 5.6 і вище вони також можуть використовуватись з таблицями InnoDB).
Повнотекстові індекси можуть бути створені тільки для стовпців типу CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT або LONGTEXT.
Опис FULLTEXT-індексу можна задати в операторі CREATE TABLE при створенні таблиці,
або добавлені за допомогою ALTER TABLE або CREATE INDEX.
Для великих наборів даних набагато швидше завантажити дані в таблицю, у якої немає FULLTEXT индексу, а після цього створити індекс, ніж завантажити дані в таблицю, у якої вже існує FULLTEXT індекс.
Повнотекстовий пошук можна використовувати, як предметний покажчик: зберігає список слів в стовпцю з посиланнями на їх місця в стовпцю.
Під словом розуміють сукупність з не менш як трьох символів, серед яких немає символу «пробіл». Будь-яке слово (з нульовою смисловою цінністю), що присутнє в стоп-списку (stopword) ігнорується. Наприклад, пошук за словом «УжНУ» у базі даних новин УжНУ не призводить до якихось результатів, оскільки це слово присутнє більш ніж у половині рядків. По суті, це стоп-слово.
Структура повнотекстового індексу:
FULLTEXT [<Ім’я індексу>] (<Список стовпців>)
Приклад створення повнотекстового індексу FULLTEXT:
CREATE TABLE articles ( id int(10) unsigned NOT NULL auto_increment, title varchar(200) default NULL, body text, PRIMARY KEY (id), FULLTEXT KEY ft1 (title, body), FULLTEXT KEY ft2 (body) ) ENGINE = MyISAM DEFAULT CHARACTER SET = utf8;
В данному випадку можна шукати рядки за допомогою індексу ft1 по полям title і body, або по індексу ft2 лише по полю body.
На основі повнотекстового пошуку може бути визначена релевантність – порівняльна міра відповідності запиту знайденій інформації.
Повнотекстовий пошук. Конструкція MATCH-AGAINST
Для повнотекстового пошуку використовується конструкція
MATCH(filelds)… AGAINST(words).
Вона повертає умовну релевантність, але спосіб її обчислень залежить від вибраного режиму.
Пошук у всіх режимах регістрозалежний.
MATCH-AGAINST IN NATURAL LANGUAGE MODE. Це основний вид пошуку (використовується по замовчуванню).
SELECT * FROM articles WHERE MATCH (title, body) AGAINST ('database');
Шукається слово database в полях title і body на основі індексу ft1. Результат автоматично відсортований по релевантності. Цу при умові, якщо блок WHERE містить конструкцію MATCH-AGAINST і не задано сортування ORDER BY.
Релевантність.
Приклад:
SELECT *, MATCH (title, body) AGAINST ('database') as Rel FROM articles WHERE MATCH (title, body) AGAINST ('database') > 0;
В змінній Rel буде обчислена релевантність. Вона залежить від кількості слів в полях tilte і body і того, наскільки близько задане слово зустрічається до початку тексту, відношення кількості знайдених слів до кількості всіх слів в полі, і.т.д.
Релевантність буде ненульовою, якщо слово database зустрінеться в title або в body. Якщо database зустрінеться і у title і у body, то релевантності буде вищою, ніж у випадку, коли воно двічі буде в body.
Саме по собі значення релевантності неістотне. Це лиши порівняльна характеристика, по якій можна сортувати результати вибірки.
Для IN NATURAL LANGUAGE MODE діє правило «50% threshold»: якщо слово зустрічається більше як у 50% всіх значень поля, то воно не буде враховуватисть у результаті.
Повнотекстовий пошук. MATCH-AGAINST IN BOOLEAN MODE
У бінарному режимі релевантність обчислюється, як умовна міра співпадання заданого шаблону. Є можливість вказати логічні оператори. Крім того, відсутнє автоматичне сортування, якщо є умова WHERE.
У логичному режимі повнотекстового пошуку підтримуються наступні оператори:
+ (знак „плюс”): знак „плюс” перед словом вказує, що це слово повинно міститись у кожному рядку, що повертається в результаті.
- (знак „минус”): знак „мінус” перед словом вказує, що це слово не повинно міститись
в жодному результуючому рядку.
За замовчанням (якщо не не вказані «+», або «-» ) дане слово не є обов’язковим, але рядки, що його містять, будуть оцінюватись вище.
Використовують ще інші знаки: ” (подвійні лапки), ~ (тiльда), * (зірочка), > (більше), < (менше).
Наприклад, оператор < зменшує вклад слова у величину релевантності, а оператор > - збільшує її.
Повнотекстовий пошук. Зв'язок „багато-до-багатьох”
Для сортування можна використати аліас:
SELECT *, MATCH (title, body) AGAINST ('+database MySQL' IN BOOLEAN MODE) AS rel FROM articles WHERE MATCH (title, body) AGAINST ('+database MySQL' IN BOOLEAN MODE) ORDER BY rel;
Будуть виведені всі записи, які містять слово database, але якщо запис містить слово MySQL, то його релевантність буде вищою.
При використанні повнотекстового пошуку інколи можна реалізувати співвідношення багато-до-багатьох без зв'язуючої таблиці.
Пр. Нехай є дві великі таблиці: користувачі і групи користувачів. Кожний користувач має відношення до значної кількості різних груп, які в свою чергу містять велику кількість користувачів. При використанні зв'язуючої таблиці запити містять зв'язки трьох таблиць, що навіть при індексуванні може виконуватись довго.
Значно швидше буде виконана команда:
SELECT * FROM groups WHERE MATCH (groups) AGAINST ('+user2' IN BOOLEAN MODE);
Релевантність, як міра відношення одного об'єкту до іншого
1). Алгоритм для знаходження статей, «схожих» на дану статтю. Беруться теги даної статті, і виконується повнотекстовий запит по полю з тегами всіх інших статей з сортуванням по релевантності. На початку будуть записані ті статті, які містять максимальне співпадіння по тегам.
2). Без врахування тегів. Якщо статті проіндексовані для повнотекстового пошуку, то з індексу вибираються декілька (наприклад, десять) найбільш використованих слів, потім виконується пошук по ним.
Приклад. Знайти користувачів по інтересам для заданого користувача. Використовуючи повнотекстового пошук легко знайти інших користувачів, у яких інтереси найбільше відповідають даним.
Зауваження. Потрібно врахувати, що самі індекси можуть займати дуже багато місця на диску, а будь-які зміни в таблицях потребують багато часу.