Дополнительные функции MySQL

Содержание

Битовые функции

Побитовое ИЛИ ( | )

Побитовое И ( & )

Побитовый XOR (побитовое сложение по модулю 2) ( ^ )

Сдвиг числа влево ( << )

Сдвиг числа вправо ( >> )

Инвертировать биты ( ~ )

BIT_COUNT(N)

Функции шифрования

AES_ENCRYPT(строка, строка_ключа)

AES_DECRYPT(зашифрованная_строка, строка_ключа)

DECODE(зашифрованная_строка, строка_пароля)

ENCODE(строка, строка_пароля)

DES_DECRYPT(зашифрованная_строка [, строка_ключа])

DES_ENCRYPT(строка[, (номер_ключа | строка_ключа)])

ENCRYPT(строка [, нач])

MD5(строка)

PASSWORD(строка)

Информационные функции

BENCHMARK(количество, выражение)

CHARSET(строка)

COERCIBILITY(строка)

COLLATION(строка)

CONNECTION_ID()

CURRENT_USER()

DATABASE()

FOUND_ROWS()

LAST_INSERT_ID()

LAST_INSERT_ID(выражение)

USER()

SESSION_USER()

SYSTEM_USER()

VERSION()

Прочие функции

FORMAT(X,D)

GET_LOCK(строка, таймаут)

INET_ATON(выражение)

INET_NTOA(выражение)

IS_FREE_LOCK(строка)

MASTER_POS_WAIT(имя_журнала, позиция_в_журнале [, таймаут])

RELEASE_LOCK(строка)

UUID()

Битовые функции

MySQL использует для двоичных операций 64-битовые величины BIGINT, следовательно, для двоичных операторов максимальный диапазон составляет 64 бита.

Побитовое ИЛИ ( | )

mysql> SELECT 29 | 15;
    -> 31

Побитовое И ( & )

mysql> SELECT 29 & 15;
    -> 13

Побитовый XOR (побитовое сложение по модулю 2) ( ^ )

mysql> SELECT 1 ^ 1;
    -> 0
mysql> SELECT 1 ^ 0;
    -> 1
mysql> SELECT 11 ^ 3;
    -> 8

Сдвиг числа влево ( << )

mysql> SELECT 1 << 2;
    -> 4

Сдвиг числа вправо ( >> )

mysql> SELECT 4 >> 2;
    -> 1

Инвертировать биты ( ~ )

mysql> SELECT 5 & ~1;
    -> 4

BIT_COUNT(N)

Возвращает количество битов аргумента N, которые установлены в единицу

mysql> SELECT BIT_COUNT(29);
    -> 4

Функции шифрования

Функции, описанные в данном разделе, шифруют и дешифруют значения данных. Если вы хотите сохранять результаты функции шифрования, которые могут иметь произвольные байтовые значения, применяйте столбцы типа BLOB вместо CHAR или VARCHAR, чтобы избежать потенциальных проблем с удалением завершающих пробелов, которые изменяют значения данных.

AES_ENCRYPT(строка, строка_ключа)

AES_DECRYPT(зашифрованная_строка, строка_ключа)

Эти функции позволяют выполнять шифрование и дешифрацию данных с использованием официального алгоритма AES (Advanced Encryption Standard), ранее известного как "Rijndael". Применяется кодирование с 128-разрядным ключом, но можно расширить его до 256 разрядов, должным образом изменив исходные тексты. Длина ключа 128 бит выбрана, поскольку он работает намного быстрее и при этом обеспечивает приемлемый уровень безопасности.

Входные аргументы могут иметь любую длину. Если любой из них равен NULL, результатом функции также будет NULL.

Поскольку AES - алгоритм блочного типа, дополнение применяется для строк с нечетным количеством символов, и поэтому длина результирующей строки может быть рассчитана как 16* (trunc (длина_строки/16) +1).

Если функция AES_DECRYPT() обнаруживает неверные данные или неправильное дополнение, она возвращает NULL. Однако существует вероятность, что AES_DECRYPT() вернет значение, не равное NULL (возможно, "мусор"), если входные данные или ключ неверны.

Вы можете использовать AES-функции для сохранения данных в зашифрованной форме, модифицировав существующие запросы:

INSERT INTO t VALUES (1, AES_ENCRYPT('text', 'password'));

Можно обеспечить даже более высокий уровень безопасности, если не передавать значение ключа для каждого запроса, а сохранять его в переменной сервера во время подключения, например:

SELECT @password:='my password';
INSERT INTO t VALUES (1, AES_ENCRYPT('text', 'password'));

Функции AES_ENCRYPT() и AES_DECRYPT() были добавлены в MySQL 4.0.2 и могут рассматриваться как наиболее криптографически безопасные функции, доступные в MySQL на текущий момент.

DECODE(зашифрованная_строка, строка_пароля)

Расшифровывает строку зашифрованная_строка, используя значение строка_пароля в качестве пароля. Аргумент зашифрованная_строка должен быть строкой, ранее возвращенной функцией ENCODE().

ENCODE(строка, строка_пароля)

Шифрует строку строка, используя значение строка_пароля в качестве пароля. Для расшифровки результата применяется функция DECODE(). Результатом является бинарная строка той же длины, что и строка. Если нужно сохранить ее в столбце, применяйте тип BLOB.

DES_DECRYPT(зашифрованная_строка [, строка_ключа])

Расшифровывает строку зашифрованная_строка, зашифрованную с помощью DES_ENCRYPT(). В случае ошибки возвращает NULL. Следует отметить, что эта функция работает, только если MySQL настроен на поддержку SSL. Если не указан аргумент строка_ключа, DES_DECRYPT() проверяет первый байт зашифрованной строки для определения номера DES-ключа, использованного при шифровании исходной строки, а затем читает ключ из файла DES-ключей для расшифровки сообщения. Чтобы это работало, пользователь должен иметь привилегию SUPER. Файл ключей может быть указан с помощью опции сервера --des-key-file.

Если вы передаете этой функции аргумент строка_ключа, он используется в качестве ключа при расшифровке сообщения.

Если аргумент зашифрованная_строка не выглядит как зашифрованная строка, MySQL вернет строку зашифрованная_строка без изменений.

DES_ENCRYPT(строка[, (номер_ключа | строка_ключа)])

Шифрует строку с помощью заданного ключа, используя тройной DES-алгоритм. В случае ошибки возвращает NULL.

Следует отметить, что эта функция работает, только если MySQL настроен на поддержку SSL. Ключ шифрования выбирается на базе второго аргумента DES_ENCRYPT(). Если таковой указан, то берётся первый ключ из используемого файла DES-ключей. Если задан номер_ключа, то он берётся из используемого файла DES-ключей. Если задана строка_ключа, то она используется в качестве ключа для шифрования.

Имя файла ключей указывается в опции сервера --des-key-file.

Длина строки результата рассчитывается как новая_длина = оригинальная_длина + (8 – (оригинальная_длина % 8)) + 1. Каждая строка в файле DES-ключей имеет следующий формат: номер_ключа строка_ключа_des.

Каждый номер_ключа должен быть числом в диапазоне от 0 до 9. Строки в файле могут следовать в любом порядке. строка_ключа_des - это строка, которая будет использоваться для шифрования сообщения. Между номером и ключом должен быть, по меньшей мере, один пробел. Первый ключ является ключом по умолчанию, который применяется в случае, если не указан аргумент строка_ключа в функции DES_ENCRYPT().

Можно указать MySQL на необходимость чтения новых значений ключа из файла ключей с помощью оператора FLUSH DES_KEY_FILE. Это требует наличия привилегии RELOAD.

Одна выгода от наличия набора ключей по умолчанию состоит в том, что это дает возможность приложениям проверить наличие зашифрованных значений столбцов, без необходимости предоставления конечному пользователю прав непосредственного доступа к ключам.

mysql> SELECT customer_address FROM customer_table WHERE
    -> crypted_credit_card = DES_ENCRYPT('credit_card_number');

ENCRYPT(строка [, нач])

Шифрует строку строка, используя системный вызов Unix crypt(). Необязательный аргумент нач должен быть строкой из не менее двух символов. Если аргумент нач отсутствует, то будет использовано случайное значение Unix crypt().

mysql> SELECT ENCRYPT('hello');
    -> 'VxuFAJXVARROc'

ENCRYPT() игнорирует все, кроме первых восьми символов аргумента строка. Это поведение определяется реализацией лежащего в основе системного вызова crypt().

Если функция crypt() не доступна в вашей системе, ENCRYPT() всегда возвращает NULL. По этой причине следует всегда применять вместо этой функции MD5(), поскольку эта функция представлена на всех платформах.

MD5(строка)

Вычисляет 128-разрядную контрольную сумму MD5 для аргумента строка. Значение возвращается в виде 32-разрядной шестнадцатеричной строки или же NULL, если аргумент равен NULL. Возвращаемое значение может быть использовано, например, в качестве хэш-ключа.

mysql> SELECT MD5('testing');
    -> 'ae2blfca515949e5d54fb22b8ed95575'

PASSWORD(строка)

Вычисляет и возвращает строку пароля по значению пароля строка, заданному простым текстом. Эта функция применяется для шифрования паролей, сохраняемых в столбце Password таблицы привилегий user.

mysql> SELECT PASSWORD('badpwd');
    -> '7f84554057dd964b'

Шифрование функцией PASSWORD() является однонаправленным (то есть необратимым).

Примечание: функция PASSWORD() используется системой аутентификации сервера MySQL, которая не должна быть задействованной в ваших собственных приложениях. Для этой цели вместо нее применяйте функции MD5() и SHA1().

Информационные функции

BENCHMARK(количество, выражение)

Функция BENCHMARK() выполняет выражение в точности количество раз. Она может использоваться для определения того, насколько быстро MySQL выполняет выражение. Возвращаемый результат всегда равен 0. Предполагаемое применение - в среде клиента mysql, который сообщает время выполнения запроса:

mysql> SELECT BENCHMARK(1000000,ENCODE("hello","goodbye"));
+----------------------------------------------+
| BENCHMARK(1000000,ENCODE("hello","goodbye")) |
+----------------------------------------------+
|                                            0 |
+----------------------------------------------+
1 row in set (4.74 sec)

Время, которое сообщает mysql - это время обслуживания клиента, а не потраченное центральным процессором время на стороне сервера. Рекомендуется выполнить BENCHMARK() несколько раз, и интерпретировать результат в зависимости от степени загруженности сервера.

CHARSET(строка)

Возвращает набор символов аргумента строка.

mysql> SELECT CHARSET('abc');
    -> 'latinl'

mysql> SELECT CHARSET(CONVERT('abc' USING utf8));
    -> 'utf8'

mysql> SELECT CHARSET(USER());
    -> 'utf8'

COERCIBILITY(строка)

Возвращает значение принуждения сопоставления для аргумента строка.

mysql> SELECT COERCIBILITY('abc' COLLATE latinl_swedish_сi);
    -> 0

mysql> SELECT COERCIBILITY('abc') ;
    -> 3

mysql> SELECT COERCIBILITY(USER());
    -> 2

Возвращаемые значения имеют следующий смысл:

0 - явное сравнение

1 - нет сравнения

2 - неявное сравнение

3 - принуждаемое

Меньшие значения обладают большим приоритетом.

COLLATION(строка)

Возвращает наименование порядка сопоставления символьного набора для заданного аргумента строка.

mysql> SELECT COLLATION('abc');
    -> 'latinl_swedish_ci'

CONNECTION_ID()

Возвращает идентификатор соединения (идентификатор потока) текущего сеанса. Каждое клиентское соединение получает свой собственный уникальный идентификатор.

mysql> SELECT CONNECTION_ID();
    -> 23786

CURRENT_USER()

Возвращает комбинацию имени пользователя и имени хоста после аутентификации в текущем сеансе. Это значение соответствует пользовательской учетной записи MySQL, которая определяет ваши права доступа. Оно может отличаться от значения, возвращаемого функцией USER().

mysql> SELECT USER();
    -> 'davida@localhost'

mysql> SELECT * FROM mysql.user;

ERROR 1044: Access denied for user: '@localhost' to database 'mysql'

mysql> SELECT CURRENT_USER();
    -> '@localhost'

Приведенный выше пример иллюстрирует, что, несмотря на то, что клиент имеет имя davida (как показывает функция USER ), сервер аутентифицировал клиента, использующего анонимный доступ (что видно по пустой части имени пользователя в значении CURRENT_USER() ). Единственной причиной, почему такое может случиться, является отсутствие учетной записи для davida в таблице привилегий.

DATABASE()

Возвращает имя базы данных по умолчанию (текущей базы данных).

mysql> SELECT DATABASE();

    -> 'test1'

Если текущей базы данных нет, DATABASE() возвращает NULL.

FOUND_ROWS()

Оператор SELECT может включать конструкцию LIMIT для ограничения количества строк, которые сервер возвращает клиенту. В некоторых случаях желательно знать, сколько строк сервер вернул бы без конструкции LIMIT, но без повторного выполнения запроса. Чтобы получить значение счетчика строк, включите опцию SQL_CALC_FOUND_ROWS в состав оператора SELECT, после чего вызовите FOUND_ROWS():

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
    -> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

Второй оператор SELECT вернет число, показывающее, сколько строк первый оператор SELECT вернул бы, будь он без конструкции LIMIT.

Следует отметить, что когда используется SELECT SQL_CALC_FOUND_ROWS, то MySQL приходится посчитать, сколько строк будет в полном результирующем наборе. Однако это делается быстрее, чем если запустить запрос снова без конструкции LIMIT, поскольку результирующий набор не приходится отсылать клиенту.

SQL_CALC_FOUND_ROWS и FOUND_ROWS() могут оказаться удобными в ситуациях, когда нужно ограничить число строк, возвращаемых запросом, но при этом определить количество строк в полном результирующем наборе, не запуская запрос снова. Примером может служить Web-сценарий, который представляет постраничный список ссылок на страницы, содержащие другие разделы результата поиска.

Функция FOUND_ROWS() позволяет определить, сколько других страниц необходимо для отображения оставшейся части результата.

Применение SQL_CALC_FOUND_ROWS и FOUND_ROWS() более сложно для запросов с UNION, чем для простых операторов SELECT, потому что LIMIT может встретиться в UNION во многих местах. Они могут касаться отдельных операторов SELECT в составе UNION либо общего результата UNION в целом.

Цель SQL_CALC_FOUND_ROWS для UNION состоит в том, что он должен вернуть количество строк, которые будут возвращены без глобального LIMIT. Условия применения SQL_CALC_FOUND_ROWS с UNION перечислены ниже:

Ключевое слово SQL_CALC_FOUND_ROWS должно указываться в первом операторе SELECT.
Значение FOUND_ROWS() будет точным только при условии применения UNION ALL. Если указано UNION без ALL, происходит исключение дубликатов, и значение FOUND_ROWS() будет лишь приблизительным.
Если в UNION не присутствует LIMIT, то SQL_CALC_FOUND_ROWS игнорируется и возвращается количество строк во временной таблице, которая создается для выполнения UNION.

LAST_INSERT_ID()

LAST_INSERT_ID(выражение)

Возвращает последнее автоматически сгенерированное значение, которое было вставлено в столбец AUTO_INCREMENT.

mysql> SELECT LAST_INSERT_ID();
    -> 195

Последний идентификатор, который был сгенерирован, поддерживается на сервере на основе соединений. Это означает, что значение, возвращаемое клиенту, соответствует последнему сгенерированному значению AUTO_INCREMENT в сеансе данного клиента. Оно никак не может быть затронуто другими клиентами, равно как не требует блокировок или транзакций.

Значение, возвращаемое LAST_INSERT_ID() не изменяется, если вы обновляете столбец AUTO_INCREMENT в строке не с помощью "магических" значений (то есть, не NULL и не 0 ).

Если вы вставляете много строк одним оператором, LAST_INSERT_ID() возвращает значение для первой вставленной строки. Цель этого состоит в том, чтобы облегчить воспроизведение того же оператора INSERT на другом сервере.

Если указан аргумент выражение, значение аргумента возвращается функцией и запоминается как следующее значение, которое LAST_INSERT_ID() вернет при следующем вызове. Это можно использовать для эмуляции последовательностей:

Создать таблицу для хранения счетчика последовательности и инициализировать его:

mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES(0);

Использовать таблицу для генерации последовательности чисел:

mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+l);
mysql> SELECT LAST_INSERT_ID();

Оператор UPDATE увеличивает счетчик последовательности и заставляет следующий вызов LAST_INSERT_ID() возвращать измененное значение.

Вы можете генерировать последовательности без вызова LAST_INSERT_ID(), но польза от ее применения заключается в том, что значение идентификатора поддерживается сервером как последнее автоматически сгенерированное значение.

Это обеспечивает безопасное использование в многопользовательской среде, поскольку множество клиентов могут отправлять операторы UPDATE и получать свои собственные значения последовательности через оператор SELECT (или mysql_insert_id() ), никак не влияя и не подвергаясь влиянию других клиентов, для которых генерируются их собственные значения последовательности.

USER()

SESSION_USER()

SYSTEM_USER()

Возвращает имя текущего пользователя MySQL и имя хоста, с которого он подключился.

mysql> SELECT USER();
    -> 'davida@localhost'

Значение представляет имя пользователя, которое было указано во время подключения к серверу, и имя компьютера-хоста, с которого произошло подключение. Возвращаемое значение может отличаться от того, которое выдает CURRENT_USER().

Вы можете извлечь имя пользователя, независимо от того, включает ли значение имя хоста или нет, следующим образом:

mysql> SELECT SUBSTRING_INDEX(USER(),'@',1);
    -> 'davida'

VERSION()

Возвращает строку, содержащую информацию о версии сервера MySQL:

mysql > SELECT VERSION();
     -> '4.1.2-alpha-log'

Следует отметить, что если строка версии заканчивается на '-log', это означает, что регистрация в журнале включена.

Прочие функции

FORMAT(X,D)

Форматирует число X в формате, подобном ' #,###,###.## ', округленное до D разрядов, и возвращает результат в виде строки. Если D равно 0, результат не имеет десятичной точки или дробной части.

mysql> SELECT FORMAT(12332.123456, 4) ;
    -> '12,332.1235'

mysql> SELECT FORMAT(12332.1,4) ;
    -> '12,332.1000'

mysql> SELECT FORMAT(12332.2,0) ;
    -> '12,332'

GET_LOCK(строка, таймаут)

Пытается получить блокировку по имени, заданном строкой строка, с таймаутом длительностью таймаут секунд. Возвращает 1, если блокировка получена успешно, 0, если время ожидания превысило таймаут (например, из-за того, что другой клиент уже заблокировал это имя), либо NULL, если произошла ошибка (такая как переполнение памяти или уничтожение потока командой mysqladmin kill ). Если у вас есть блокировка, полученная через GET_LOCK(), она снимается после выполнения RELEASE_LOCK(), нового вызова GET_LOCK() либо разрыва соединения (как нормального, так и ненормального).

Эта функция может использоваться для реализации блокировок приложений или для эмуляции блокировок записей. Имена блокируются в глобальном контексте сервера. Если имя блокировано одним клиентом, GET_LOCK() блокирует любой запрос другого клиента на получение блокировки с тем же именем. Это позволяет клиентам согласовывать попытки доступа к общим ресурсам.

mysql> SELECT GET_LOCK('lock1',10) ;
-> 1

mysql> SELECT IS_FREE_LOCK('lock2');
-> 1

mysql> SELECT GET_LOCK('lock2',10);
-> 1

mysql> SELECT RELEASE_LOCK('lock2');
-> 1

mysql> SELECT RELEASE_LOCK('lockl');
-> NULL

Следует отметить, что второй вызов RELEASE_LOCK() возвращает NULL, поскольку блокировка ' lock1 ' была автоматически снята вторым вызовом GET_LOCK().

INET_ATON(выражение)

Принимает сетевой адрес, представленный четырьмя числами с разделителем-точкой, и возвращает целое, представляющее числовое значение адреса. Адрес может быть 4- или 8-байтным.

mysql> SELECT INET_ATON('209.207.224.40');
-> 3520061480

Сгенерированное число всегда содержит байты в порядке, заданном в сетевом адресе. Для только что приведенного примера оно вычисляется как 209 * 256³ + 207 * 256² + 224 * 256 + 40.

INET_ATON() также понимает IP-адреса в сокращенной форме:

mysql> SELECT INET_ATON('127.0.0.1'), INET_ATON('127.1');
-> 2130706433, 2130706433

INET_NTOA(выражение)

Принимает сетевой адрес в виде числа (4- или 8- байтного), возвращает адрес, представленный строкой, состоящей из четырех чисел, разделенных точкой.

mysql> SELECT INET_NTOA(3520061480);
-> '209.207.224.40'

IS_FREE_LOCK(строка)

Проверяет, свободна ли блокировка с именем строка. Возвращает 1, если блокировка свободна (никем не используется), 0, если занята, и NULL в случае ошибки.

IS_USED_LOCK(строка). Проверяет, используется ли блокировка с именем строка (то есть, установлена ли она). Если это так, возвращает идентификатор соединения клиента, который удерживает блокировку. В противном случае возвращает NULL.

MASTER_POS_WAIT(имя_журнала, позиция_в_журнале [, таймаут])

Эта функция удобна для управления синхронизацией главный/подчиненный. Блокирует главный сервер до тех пор, пока подчиненный сервер не прочитает и не проведет все изменения вплоть до указанной позиции в бинарном журнале главного сервера. Возвращаемое значение представляет количество событий в журнале, обработку которых нужно выполнить системе синхронизации, чтобы дойти до указанной позиции. Функция возвращает NULL, если поток SQL подчиненного сервера не запущен, либо информация о главном сервере не инициализирована на подчиненном, либо указаны неправильные аргументы. Возвращает -1, если истекло время таймаута. Если подчиненный сервер уже достиг указанной позиции, функция возвращает управление немедленно.

Если задано значение таймаут, MASTER_POS_WAIT() прекращает ожидание, когда истекают таймаут секунд. Значение таймаут может быть больше 0, а если его значение равно 0 или является отрицательным, то ожидания нет.

RELEASE_LOCK(строка)

Снимает блокировку с именем строка, которая была получена с помощью функции GET_LOCK(). Возвращает 1, если блокировка снята, 0, если блокировка была установлена другим потоком (а значит, не может быть снята), и NULL, если блокировка с таким именем не существует. Блокировка не существует, если не была установлена вызовом GET_LOCK(), либо она уже снята.

UUID()

Возвращает Универсальный Уникальный Идентификатор (Universal Unique Identifier - UUID). Идентификатор UUID спроектирован как число, которое является глобально уникальным во времени и пространстве. Ожидается, что два вызова UUID сгенерируют два разных значения, даже если эти два вызова произойдут на двух разных компьютерах, которые не подключены друг к другу.

UUID - это 128-разрядное число, представленное в виде строки, состоящей из пяти шестнадцатеричных чисел в формате aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:

Первые три числа генерируются на основе временной метки.
Четвертое число предохраняет темпоральную уникальность в случае, если значение временной метки теряет монотонность (например, из-за перехода на летнее время и обратно).
Пятое число - это номер узла IЕЕ 802, который представляет пространственную уникальность. Случайное число подставляется в случае, если последнее недоступно (например, если компьютер-хост не имеет сетевой платы Ethernet, или нет возможности извлечь аппаратный адрес интерфейса вашего компьютера). В этом случае пространственная уникальность не может быть гарантирована. Однако, несмотря на это, коллизии крайне маловероятны. В настоящее время МАС-адрес интерфейса принимается во внимание только в средах FreeBSD и Linux. В других операционных системах MySQL использует случайно сгенерированное 48-разрядное число.

mysql> SELECT UUID();
-> '6ccd780c-baba-1026-9564-0040f4311e29'

к оглавлению

Знаете ли Вы, что диаграмма классов, Class diagram - это методология объектно-ориентированного проектирования, предназначенная для представления статической структуры модели системы в терминологии классов объектно-ориентированного программирования.

НОВОСТИ ФОРУМА

Рыцари теории эфира