Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Explore secondary functions in MariaDB Server. This section details various SQL functions that support specialized operations, enhancing data manipulation and analysis beyond core functionalities.
Perform bitwise logic and manipulation. This section covers operators like AND, OR, XOR, and functions for shifting bits or counting set bits in binary data.
Shift bits to the left. This operator shifts the binary representation of a number to the left by a specified number of positions, filling with zeros.
value1 << value2Converts a longlong (BIGINT) number (value1) to binary and shifts value2 units to the left.
This page is licensed: GPLv2, originally from
Learn about encryption, hashing, and compression functions. This section details SQL functions for securing data, generating hashes, and compressing/decompressing information within your database.
Explore specialized utilities. This category includes functions for benchmarking, managing UUIDs, converting IP addresses, and handling binary data formats.
ENCODE is not considered cryptographically secure, and should not be used for password encryption.
Encrypt str using pass_str as the password. To decrypt the result, use DECODE().
The result is a binary string of the same length as str.
The strength of the encryption is based on how good the random generator is.
It is not recommended to rely on the encryption performed by the ENCODE function. Using a salt value (changed when a password is updated) will improve matters somewhat, but for storing passwords, consider a more cryptographically secure function, such as SHA2().
This page is licensed: GPLv2, originally from fill_help_tables.sql
ENCODE(str,pass_str)Decrypts a string encrypted with DES_ENCRYPT(). If an error occurs, this function returns NULL.
This function works only if MariaDB has been configured with TLS support.
If no key_str argument is given, DES_DECRYPT() examines the first byte of the encrypted string to determine the DES key number that was used to encrypt the original string, and then reads the key from the DES key file to decrypt the message. For this to work, the user must have the SUPER privilege. The key file can be specified with the--des-key-file server option.
If you pass this function a key_str argument, that string is used as the key for decrypting the message.
If the crypt_str argument does not appear to be an encrypted string, MariaDB returns the given crypt_str.
This page is licensed: GPLv2, originally from fill_help_tables.sql
DES_DECRYPT(crypt_str[,key_str])SELECT 1 << 2;
+--------+
| 1 << 2 |
+--------+
| 4 |
+--------+Control operator precedence. Parentheses are used to group expressions and enforce the order of evaluation in complex operations.
Parentheses are sometimes called precedence operators - this means that they can be used to change the other operator's precedence in an expression. The expressions that are written between parentheses are computed before the expressions that are written outside. Parentheses must always contain an expression (that is, they cannot be empty), and can be nested.
For example, the following expressions could return different results:
NOT a OR b
NOT (a OR b)
In the first case, NOT applies to a, so if a is FALSE or b is TRUE, the expression returns TRUE. In the second case, NOT applies to the result of a OR b, so if at least one of a or b is TRUE, the expression is TRUE.
When the precedence of operators is not intuitive, you can use parentheses to make it immediately clear for whoever reads the statement.
The precedence of the NOT operator can also be affected by the HIGH_NOT_PRECEDENCE flag.
Parentheses must always be used to enclose .
Parentheses can also be used in a statement between multiple tables to determine which tables must be joined first.
Also, parentheses are used to enclose the list of parameters to be passed to built-in functions, user-defined functions and stored routines. However, when no parameter is passed to a stored procedure, parentheses are optional. For builtin functions and user-defined functions, spaces are not allowed between the function name and the open parenthesis, unless the IGNORE_SPACE is set. For stored routines (and for functions if IGNORE_SPACE is set) spaces are allowed before the open parenthesis, including tab characters and new line characters.
If there are more open parentheses than closed parentheses, the error usually looks like this:
Note the empty string.
If there are more closed parentheses than open parentheses, the error usually looks like this:
Note the quoted closed parenthesis.
This page is licensed: CC BY-SA / Gnu FDL
Generate random bytes. This function returns a binary string of random bytes of a specified length, suitable for cryptographic use.
The RANDOM_BYTES function generates a binary string of random bytes.
Given a length from 1 to 1024, generates a binary string of length consisting of random bytes generated by the SSL library's random number generator.
See the RAND_bytes() function documentation of your SSL library for information on the random number generator. In the case of , a cryptographically secure pseudo random generator (CSPRNG) is used.
Statements containing the RANDOM_BYTES function are .
An error occurs if length is outside the range 1 to 1024.
This page is licensed: CC BY-SA / Gnu FDL
Calculate SHA-1 checksum. This function computes the SHA-1 160-bit checksum for a string, returning a 40-character hexadecimal string.
SHA1(str), SHA(str)Calculates an SHA-1 160-bit checksum for the string str, as described in RFC 3174 (Secure Hash Algorithm).
The value is returned as a string of 40 hex digits, or NULL if the argument was NULL. The return value is a nonbinary string in the connection , determined by the values of the and system variables.
This page is licensed: GPLv2, originally from
Synonym for DATABASE(). Returns the name of the default database currently in use.
SCHEMA()This function is a synonym for DATABASE().
This page is licensed: GPLv2, originally from
ENCODE('not so secret text', CONCAT('random_salt','password'))Retrieve system and session metadata. This section details functions to access database names, user details, version info, and query statistics like row counts.
NULLThis page is licensed: GPLv2, originally from fill_help_tables.sql
CHARSET(str)NULLThis page is licensed: GPLv2, originally from fill_help_tables.sql
UNCOMPRESS(string_to_uncompress)AES_ENCRYPT() and AES_DECRYPT() allow encryption and decryption of data using the official AES (Advanced Encryption Standard) algorithm, previously known as "Rijndael." Encoding with a 128-bit key length is used (from , this is the default, and can be changed). 128 bits is much faster and is secure enough for most purposes.
AES_ENCRYPT() encrypts a string str using the key key_str, and returns a binary string.
AES_DECRYPT() decrypts the encrypted string and returns the original string.
The input arguments may be any length. If either argument is NULL, the result of this function is also NULL.
Because AES is a block-level algorithm, padding is used to encode uneven length strings and so the result string length may be calculated using this formula:
If AES_DECRYPT() detects invalid data or incorrect padding, it returns NULL. However, it is possible for AES_DECRYPT() to return a non-NULL value (possibly garbage) if the input data or the key is invalid.
MariaDB starting with
The function supports an initialization vector, and control of the block encryption mode. The default mode is specified by the block_encryption_mode system variable, which can be changed when calling the function with a mode. mode is aes-{128,192,256}-{ecb,cbc,ctr} for example: "AES-128-cbc".
AES_ENCRYPT(str, key) can no longer be used in persistent virtual columns (and the like).
The function does not support an initialization vector.
SELECT HEX(AES_ENCRYPT('foo', 'bar', '0123456789abcdef', 'aes-256-cbc')) AS x;
+----------------------------------+
| x |
+----------------------------------+
| 42A3EB91E6DFC40A900D278F99E0726E |
+----------------------------------+INSERT INTO t VALUES (AES_ENCRYPT('text',SHA2('password',512)));RANDOM_BYTES() is a function for generating good encryption keys for AES_ENCRYPT.
KDF() is a key derivation function which is useful if an authentication validation against the value is required without data being able to be decrypted.
This page is licensed: GPLv2, originally from fill_help_tables.sql
AES_ENCRYPT(str, key, [, iv [, mode]])AES_ENCRYPT(str,key_str)This function allows decryption of data using the official AES (Advanced Encryption Standard) algorithm. For more information, see the description of AES_ENCRYPT().
The function supports an initialization vector, and control of the block encryption mode. The default mode is specified by the block_encryption_mode system variable, which can be changed when calling the function with a mode. mode is aes-{128,192,256}-{ecb,cbc,ctr} for example: "AES-128-cbc".
For modes that require it, the initialization_vector iv should be 16 bytes (it can be longer, but the extra bytes are ignored). A shorter iv, where one is required, results in the function returning NULL. Calling RANDOM_BYTES(16) will generate a random series of bytes that can be used for the iv.
Examples
SELECT HEX(AES_ENCRYPT('foo', 'bar', '0123456789abcdef', 'aes-128-ctr')) AS x;
+--------+
| x |
+--------+
| C57C4B |
+--------+
SELECT AES_DECRYPT(x'C57C4B', 'bar'
The function does not support an initialization vector.
This page is licensed: GPLv2, originally from fill_help_tables.sql
AES_ENCRYPT(crypt_str, key_str, [, iv [, mode]])AES_DECRYPT(crypt_str,key_str)UNCOMPRESSED_LENGTH() returns NULL or an incorrect result if the string is not compressed.Returns MYSQL_TYPE_LONG, or int(10), if the result fits within 32-bits.
This page is licensed: GPLv2, originally from fill_help_tables.sql
UNCOMPRESSED_LENGTH(compressed_string)This page is licensed: CC BY-SA / Gnu FDL
~This page is licensed: GPLv2, originally from fill_help_tables.sql
&countThis page is licensed: GPLv2, originally from fill_help_tables.sql
BENCHMARK(count,expr)SELECT BENCHMARK(1000000,ENCODE('hello','goodbye'));
+----------------------------------------------+
| BENCHMARK(1000000,ENCODE('hello','goodbye')) |
+----------------------------------------------+
| 0 |
+----------------------------------------------+
1 row in set (0.21 sec)This page is licensed: GPLv2, originally from fill_help_tables.sql
SYSTEM_USER()MYSQL_TYPE_LONG, or int(10).This page is licensed: GPLv2, originally from fill_help_tables.sql
CONNECTION_ID()SELECT TRUE, true, FALSE, false;
+------+------+-------+-------+
| TRUE | TRUE | FALSE | FALSE |
+------+------+-------+-------+
| 1 | 1 | 0 | 0 |
+------+------+-------+-------+RANDOM_BYTES(length)SELECT BIT_COUNT(29), BIT_COUNT(b'101010');
+---------------+----------------------+
| BIT_COUNT(29) | BIT_COUNT(b'101010') |
+---------------+----------------------+
| 4 | 3 |
+---------------+----------------------+SELECT SHA1('some boring text');
+------------------------------------------+
| SHA1('some boring text') |
+------------------------------------------+
| af969fc2085b1bb6d31e517d5c456def5cdd7093 |
+------------------------------------------+Returns the current role. The current role can be set with SET ROLE or SET DEFAULT ROLE.
CURRENT_ROLE, CURRENT_ROLE()Returns the current role name. The return value is a string in the utf8 character set.
If there is no current role, NULL is returned.
returns the combination of user and host used to login. returns the account used to determine current connection's privileges.
Statements using the CURRENT_ROLE function are not .
This page is licensed: CC BY-SA / Gnu FDL
Derive a key. This function derives a key from a password using a Key Derivation Function (KDF), enhancing security for password storage.
KDF(key_str, salt [, {info | iterations} [, kdf_name [, width ]]])KDF is a key derivation function, similar to OpenSSL's EVP_KDF_derive(). The purpose of a KDF is to be slow, so if the calculated value is lost/stolen, the original key_str is not achievable easily with modern GPU. KDFs are therefore an ideal replacement for password hashes. KDFs can also pad out a password secret to the number of bits used in encryption algorithms.
For generating good encryption keys for a less expensive but cryptographically secure function like is recommended.
kdf_name is "hkdf" or "pbkdf2_hmac" (default).
Width (in bits) can be any number divisible by 8, by default it's taken from @@block_encryption_mode.
Iterations must be positive, and is 1000 by default.
Note that OpenSSL 1.0 doesn't support HKDF, so in this case NULL is returned. This OpenSSL version is still used in SLES 12 and CentOS 7.
This page is licensed: CC BY-SA / Gnu FDL
Encrypt a string using Unix crypt(). This function encrypts a string using the Unix crypt() system call, typically used for password hashing.
ENCRYPT(str[,salt])Encrypts a string using the Unix crypt() system call, returning an encrypted binary string. The salt argument should be a string with at least two characters or the returned result will be NULL. If no salt argument is given, a random value of sufficient length is used.
It is not recommended to use ENCRYPT() with utf16, utf32 or ucs2 multi-byte character sets because the crypt() system call expects a string terminated with a zero byte.
Note that the underlying crypt() system call may have some limitations, such as ignoring all but the first eight characters.
If the system variable is set to NO (because the crypt() system call is not available), the ENCRYPT function will always return NULL.
This page is licensed: GPLv2, originally from
Compress a string. This function compresses a string argument and returns the result as a binary string, useful for saving storage space.
COMPRESS(string_to_compress)Compresses a string and returns the result as a binary string. This function requires MariaDB to have been compiled with a compression library such as zlib. Otherwise, the return value is always NULL. The
compressed string can be uncompressed with .
The server system variable indicates whether a compression library is present.
This page is licensed: GPLv2, originally from
Return the authenticated user name. This function displays the user name and host name combination used by the server to authenticate the current client.
CURRENT_USER, CURRENT_USER()Returns the user name and host name combination for the MariaDB account that the server used to authenticate the current client. This account determines your access privileges. The return value is a string in the utf8 .
The value of CURRENT_USER() can differ from the value of . returns the current active role.
Statements using the CURRENT_USER function are not .
When calling CURRENT_USER() in a stored procedure, it returns the owner of the stored procedure, as defined with DEFINER.
This page is licensed: GPLv2, originally from
Perform bitwise OR. This operator compares bits and returns 1 if at least one of the corresponding bits is 1.
Return the current MariaDB user. This function returns the user name and host name provided by the client upon connection.
Returns the current MariaDB user name and host name, given when authenticating to MariaDB, as a string in the utf8 character set.
The value of USER() may differ from the value of , which is the user used to authenticate the current client. returns the currently active role.
SYSTEM_USER() is a synonym for USER().
SYSTEM_USER() and are synonyms for USER().
Statements using the USER() function or one of its synonyms are not .
To select only the IP address, use ,
This page is licensed: GPLv2, originally from
Calculate SHA-2 checksum. This function computes the SHA-2 family of hash functions (SHA-224, SHA-256, SHA-384, and SHA-512).
SHA2(str,hash_len)Given a string str, calculates an SHA-2 checksum, which is considered more cryptographically secure than its equivalent. The SHA-2 family includes SHA-224, SHA-256, SHA-384, and SHA-512, and the hash_len must correspond to one of these, i.e. 224, 256, 384 or 512. 0 is equivalent to 256.
The return value is a nonbinary string in the connection , determined by the values of the and system variables.
NULL is returned if the hash length is not valid, or the string str is NULL.
SHA2 only works if MariaDB is configured with .
This page is licensed: CC BY-SA / Gnu FDL
Analyze query results. This procedure examines the result set and suggests optimal data types for columns based on the data.
ANALYSE([max_elements[,max_memory]])This procedure is defined in the sql/sql_analyse.cc file. It examines the result from a query and returns an analysis of the results that suggests optimal data types for each column. To obtain this analysis, append PROCEDURE ANALYSE to the end of a statement:
For example:
The results show some statistics for the values returned by the query, and propose an optimal data type for the columns. This can be helpful for checking your existing tables, or after importing new data. You may need to try different settings for the arguments so that PROCEDURE ANALYSE() does not suggest the ENUM data type when it is not appropriate.
The arguments are optional and are used as follows:
max_elements (default 256) is the maximum number of distinct values that analyse notices per column. This is used by analyse to check whether the optimal data type should be of type ENUM; if there are more than max_elements distinct values, then ENUM is not a suggested type.
max_memory (default 8192) is the maximum amount of memory that analyse should allocate per column while trying to find all distinct values.
This page is licensed: GPLv2, originally from
Check validity of an IPv6 address. This function returns 1 if the argument is a valid IPv6 address string, and 0 otherwise.
Retrieve GTID position from a binary log file. This function returns the Global Transaction ID corresponding to a specific file and position in the binlog.
BINLOG_GTID_POS(binlog_filename,binlog_offset)The BINLOG_GTID_POS() function takes as input an old-style binary log position in the form of a file name and a file offset. It looks up the position in the current binlog, and returns a string representation of the corresponding position. If the position is not found in the current binlog, NULL is returned.
- Show events and their positions in the binary log
This page is licensed: CC BY-SA / Gnu FDL
Return the current user name. This function returns the user name and host name as provided by the client when connecting.
SESSION_USER()Shows the value of CURRENT_USER() when the session was created, that is, it shows a user@host pair from the , like CURRENT_USER(), but unlike CURRENT_USER() it will not change inside stored routines and views. This is SQL Standard behavior for the SESSION_USER function.
SESSION_USER() is a synonym for .
Backward-compatible behavior can be restored by setting old mode to .
This page is licensed: GPLv2, originally from
Wait for a replica to reach a log position. This function blocks until the replica has read and applied updates up to a specified position in the master's binary log.
MASTER_POS_WAIT(log_name,log_pos[,timeout,["connection_name"]])This function is useful in replication for controlling primary/replica synchronization. It blocks until the replica has read and applied all updates up to the specified position (log_name,log_pos) in the primary log. The return value is the number of log events the replica had to wait for to advance to the specified position. The function returns NULL if the replica SQL thread is not started, the replica's primary information is not initialized, the arguments are incorrect, or an error occurs. It returns -1 if
the timeout has been exceeded. If the replica SQL thread stops whileMASTER_POS_WAIT() is waiting, the function returns NULL. If the replica is past the specified position, the function returns immediately.
If a timeout value is specified, MASTER_POS_WAIT() stops waiting when timeout seconds have elapsed. timeout must be greater than 0; a zero or negative timeout means no timeout.
The connection_name is used when you are using . If you don't specify it, it's set to the value of the system variable.
Statements using the MASTER_POS_WAIT() function are not .
This page is licensed: GPLv2, originally from
Check validity of an IPv4 address. This function returns 1 if the argument is a valid IPv4 address string, and 0 otherwise.
IS_IPV4(expr)If the expression is a valid IPv4 address, returns 1, otherwise returns 0.
IS_IPV4() is stricter than , but as strict as , in determining the validity of an IPv4 address. This implies that if IS_IPV4 returns 1, the same expression will always return a non-NULL result when passed to INET_ATON(), but that the reverse may not apply.
This page is licensed: CC BY-SA / Gnu FDL
Encrypt data using DES. This function encrypts a string using the Data Encryption Standard (DES) algorithm.
DES_ENCRYPT is deprecated and will be removed in a future release.
Encrypts the string with the given key using the Triple-DES algorithm.
This function works only if MariaDB has been configured with .
The encryption key to use is chosen based on the second argument toDES_ENCRYPT(), if one was given. With no argument, the first key from the DES key file is used. With a key_num argument, the given key
number (0-9) from the DES key file is used. With a key_str argument, the given key string is used to encrypt str.
The key file can be specified with the --des-key-file server option.
The return string is a binary string where the first character isCHAR(128 | key_num). If an error occurs, DES_ENCRYPT() returns NULL.
The 128 is added to make it easier to recognize an encrypted key. If you use a string key, key_num is 127.
The string length for the result is given by this formula:
Each line in the DES key file has the following format:
Each key_num value must be a number in the range from 0 to 9. Lines in the file may be in any order. des_key_str is the string that is used to encrypt the message. There should be at least one space between the number and the key. The first key is the default key that is used if you do not specify any key argument to DES_ENCRYPT().
You can tell MariaDB to read new key values from the key file with the FLUSH DES_KEY_FILE statement. This requires the RELOAD privilege.
One benefit of having a set of default keys is that it gives applications a way to check for the existence of encrypted column values, without giving the end user the right to decrypt those values.
This page is licensed: GPLv2, originally from
Force a column name for an expression. This function is primarily used internally for replication to ensure consistent column naming.
NAME_CONST(name,value)Returns the given value. When used to produce a result set column,NAME_CONST() causes the column to have the given name. The arguments should be constants.
This function is used internally when replicating stored procedures. It makes little sense to use it explicitly in SQL statements, and it was not supposed to be used like that.
This page is licensed: GPLv2, originally from
Perform bitwise exclusive OR. This operator returns 1 only if the corresponding bits of the operands are different.
Shift bits to the right. This operator shifts the binary representation of a number to the right by a specified number of positions.
value1 >> value2Converts a longlong (BIGINT) number (value1) to binary and shifts value2 units to the right.
This page is licensed: GPLv2, originally from
Pause query execution. This function pauses the script for a specified number of seconds, returning 0 if successful or 1 if interrupted.
SLEEP(duration)Sleeps (pauses) for the number of seconds given by the duration argument, then returns 0. If SLEEP() is interrupted, it returns 1. The duration may have a fractional part given in microseconds.
Statements using the SLEEP() function are not .
This page is licensed: GPLv2, originally from
Inspect histogram data. This function allows viewing the distribution statistics stored in a histogram for query optimization analysis.
Convert a binary IPv6 address to string. This function takes a binary IPv6 address (like from INET6_ATON) and returns its human-readable string representation.
SELECT CHARSET('abc');
+----------------+
| CHARSET('abc') |
+----------------+
| latin1 |
+----------------+
SELECT CHARSET(CONVERT('abc' USING utf8));
+------------------------------------+
| CHARSET(CONVERT('abc' USING utf8)) |
+------------------------------------+
| utf8 |
+------------------------------------+
SELECT CHARSET(USER());
+-----------------+
| CHARSET(USER()) |
+-----------------+
| utf8 |
+-----------------+SELECT UNCOMPRESS(COMPRESS('a string'));
+----------------------------------+
| UNCOMPRESS(COMPRESS('a string')) |
+----------------------------------+
| a string |
+----------------------------------+
SELECT UNCOMPRESS('a string');
+------------------------+
| UNCOMPRESS('a string') |
+------------------------+
| NULL |
+------------------------+16 x (trunc(string_length / 16) + 1)SELECT UNCOMPRESSED_LENGTH(COMPRESS(REPEAT('a',30)));
+-----------------------------------------------+
| UNCOMPRESSED_LENGTH(COMPRESS(REPEAT('a',30))) |
+-----------------------------------------------+
| 30 |
+-----------------------------------------------+SELECT 3 & ~1;
+--------+
| 3 & ~1 |
+--------+
| 2 |
+--------+
SELECT 5 & ~1;
+--------+
| 5 & ~1 |
+--------+
| 4 |
+--------+SELECT 2&1;
+-----+
| 2&1 |
+-----+
| 0 |
+-----+
SELECT 3&1;
+-----+
| 3&1 |
+-----+
| 1 |
+-----+
SELECT 29 & 15;
+---------+
| 29 & 15 |
+---------+
| 13 |
+---------+SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
| 3 |
+-----------------+ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server version for the right syntax to use near '' a
t line 1ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server version for the right syntax to use near ')'
at line 1USER()DES_ENCRYPT(str[,{key_num|key_str}])SELECT ENCRYPT('encrypt me');
+-----------------------+
| ENCRYPT('encrypt me') |
+-----------------------+
| 4I5BsEx0lqTDk |
+-----------------------+SELECT BINLOG_GTID_POS("master-bin.000001", 600);SELECT IS_IPV4('1110.0.1.1');
+-----------------------+
| IS_IPV4('1110.0.1.1') |
+-----------------------+
| 0 |
+-----------------------+
SELECT IS_IPV4('48f3::d432:1431:ba23:846f');
+--------------------------------------+
| IS_IPV4('48f3::d432:1431:ba23:846f') |
+--------------------------------------+
| 0 |
+--------------------------------------+SELECT NAME_CONST('myname', 14);
+--------+
| myname |
+--------+
| 14 |
+--------+SELECT 4 >> 2;
+--------+
| 4 >> 2 |
+--------+
| 1 |
+--------+SELECT SLEEP(5.5);
+------------+
| SLEEP(5.5) |
+------------+
| 0 |
+------------+
1 row in set (5.50 sec)OLD_PASSWORD() returns the value of the old (pre-MySQL 4.1) implementation of PASSWORD() as a string, and is intended to permit you to reset passwords for any pre-4.1 clients that need to connect to a more recent MySQL server version, or any version of MariaDB, without locking them out.The return value is a nonbinary string in the connection character set and collation, determined by the values of the character_set_connection and collation_connection system variables.
The return value is 16 bytes in length, or NULL if the argument was NULL.
This page is licensed: GPLv2, originally from fill_help_tables.sql
NULLstrIf the metadata_lock_info plugin is installed, the Information Schema metadata_lock_info table contains information about locks of this kind (as well as metadata locks).
Statements using the IS_USED_LOCK function are not safe for statement-based replication.
This page is licensed: GPLv2, originally from fill_help_tables.sql
10NULLNULLstrIf the metadata_lock_info plugin is installed, the Information Schema metadata_lock_info table contains information about locks of this kind (as well as metadata locks).
Statements using the IS_FREE_LOCK function are not safe for statement-based replication.
This page is licensed: GPLv2, originally from fill_help_tables.sql
LAST_VALUE can be used as a window function.
Returns NULL if no last value exists.
As a window function:
This page is licensed: CC BY-SA / Gnu FDL
NULLThis page is licensed: GPLv2, originally from fill_help_tables.sql
SINGLE_PREC_HBDOUBLE_PREC_HBhist_typehistogramSee Histogram Based Statistics for details.
This page is licensed: CC BY-SA / Gnu FDL
DECODE_HISTOGRAM(hist_type,histogram)The function understands only binary(16) representation.
This page is licensed: CC BY-SA / Gnu FDL
NULLSCHEMA() is a synonym for DATABASE().
To select a default database, the USE statement can be run. Another way to set the default database is specifying its name at mariadb command line client startup.
This page is licensed: GPLv2, originally from fill_help_tables.sql
DATABASE()
SCHEMA()VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts.The VALUES() function is meaningful only in INSERT ... ON DUPLICATE KEY UPDATE statements and returns NULL otherwise.
This function was renamed to VALUE(), because it's incompatible with the standard Table Value Constructors syntax.
The VALUES() function can still be used but only in INSERT ... ON DUPLICATE KEY UPDATE statements; it's a syntax error otherwise.
This page is licensed: GPLv2, originally from fill_help_tables.sql
The VERSION() string may have one or more of the following suffixes:
-embedded
The server is an embedded server (libmariadbd).
-log
General logging, slow logging or binary (replication) logging is enabled.
-debug
The server is compiled for debugging.
-valgrind
The server is compiled to be instrumented with valgrind.
Some old legacy code may break because they are parsing theVERSION string and expecting a MySQL string or a simple version string like Joomla til API17, see MDEV-7780.
You can fool these applications by setting the version string from the command line or the my.cnf files with --version=....
This page is licensed: GPLv2, originally from fill_help_tables.sql
Returns NULL if the argument is not understood.
This page is licensed: CC BY-SA / Gnu FDL
INET6_NTOA(expr)NULL is returned if the argument was NULL.
This page is licensed: GPLv2, originally from fill_help_tables.sql
MD5(str)192.168.1.1 corresponds to 3232235777 since 192 x 2563 + 168 x 256 2 + 1 x 256 + 1 = 3232235777.
This page is licensed: GPLv2, originally from fill_help_tables.sql
Given a byte count, returns a string consisting of a value and the units in a human-readable format. The units will be in bytes, KiB (kibibytes), MiB (mebibytes), GiB (gibibytes), TiB (tebibytes), PiB (pebibytes) or EiB (exbibytes).
The binary prefixes (kibi, mebi, gibi, tebi, pebi and exbi) were created in December 1998 by the International Electrotechnical Commission to avoid possible ambiguity, as the widely-used prefixes kilo, mega, giga, tera, peta and exa can be used to refer to both the power-of-10 decimal system multipliers and the power-of-two binary system multipliers.
This function is similar to the Sys Schema format_bytes function, except that function does not display exbibytes.
This page is licensed: CC BY-SA / Gnu FDL
FORMAT_BYTES(double)SELECT CURRENT_ROLE;
+--------------+
| CURRENT_ROLE |
+--------------+
| NULL |
+--------------+
SET ROLE staff;
SELECT CURRENT_ROLE;
+--------------+
| CURRENT_ROLE |
+--------------+
| staff |
+--------------+SELECT LENGTH(COMPRESS(REPEAT('a',1000)));
+------------------------------------+
| LENGTH(COMPRESS(REPEAT('a',1000))) |
+------------------------------------+
| 21 |
+------------------------------------+
SELECT LENGTH(COMPRESS(''));
+----------------------+
| LENGTH(COMPRESS('')) |
+----------------------+
| 0 |
+----------------------+
SELECT LENGTH(COMPRESS('a'));
+-----------------------+
| LENGTH(COMPRESS('a')) |
+-----------------------+
| 13 |
+-----------------------+
SELECT LENGTH(COMPRESS(REPEAT('a',16)));
+----------------------------------+
| LENGTH(COMPRESS(REPEAT('a',16))) |
+----------------------------------+
| 15 |
+----------------------------------+shell> mysql --user="anonymous"
SELECT USER(),CURRENT_USER();
+---------------------+----------------+
| USER() | CURRENT_USER() |
+---------------------+----------------+
| anonymous@localhost | @localhost |
+---------------------+----------------+SELECT 2|1;
+-----+
| 2|1 |
+-----+
| 3 |
+-----+
SELECT 29 | 15;
+---------+
| 29 | 15 |
+---------+
| 31 |
+---------+shell> mysql --user="anonymous"
SELECT USER(),CURRENT_USER();
+---------------------+----------------+
| USER() | CURRENT_USER() |
+---------------------+----------------+
| anonymous@localhost | @localhost |
+---------------------+----------------+SELECT SUBSTRING_INDEX(USER(), '@', -1);
+----------------------------------+
| SUBSTRING_INDEX(USER(), '@', -1) |
+----------------------------------+
| 192.168.0.101 |
+----------------------------------+SELECT SHA2('Maria',224);
+----------------------------------------------------------+
| SHA2('Maria',224) |
+----------------------------------------------------------+
| 6cc67add32286412efcab9d0e1675a43a5c2ef3cec8879f81516ff83 |
+----------------------------------------------------------+
SELECT SHA2('Maria',256);
+------------------------------------------------------------------+
| SHA2('Maria',256) |
+------------------------------------------------------------------+
| 9ff18ebe7449349f358e3af0b57cf7a032c1c6b2272cb2656ff85eb112232f16 |
+------------------------------------------------------------------+
SELECT SHA2('Maria',0);
+------------------------------------------------------------------+
| SHA2('Maria',0) |
+------------------------------------------------------------------+
| 9ff18ebe7449349f358e3af0b57cf7a032c1c6b2272cb2656ff85eb112232f16 |
+------------------------------------------------------------------+SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]])SELECT col1, col2 FROM table1 PROCEDURE ANALYSE(10, 2000);SELECT IS_IPV6('48f3::d432:1431:ba23:846f');
+--------------------------------------+
| IS_IPV6('48f3::d432:1431:ba23:846f') |
+--------------------------------------+
| 1 |
+--------------------------------------+
1 row in set (0.02 sec)
SELECT IS_IPV6('10.0.1.1');
+---------------------+
| IS_IPV6('10.0.1.1') |
+---------------------+
| 0 |
+---------------------+new_len = orig_len + (8 - (orig_len % 8)) + 1key_num des_key_strSELECT customer_address FROM customer_table
WHERE crypted_credit_card = DES_ENCRYPT('credit_card_number');SELECT 1 ^ 1;
+-------+
| 1 ^ 1 |
+-------+
| 0 |
+-------+
SELECT 1 ^ 0;
+-------+
| 1 ^ 0 |
+-------+
| 1 |
+-------+
SELECT 11 ^ 3;
+--------+
| 11 ^ 3 |
+--------+
| 8 |
+--------+OLD_PASSWORD(str)IS_USED_LOCK(str)IS_FREE_LOCK(str)LAST_VALUE(expr,[expr,...])LAST_VALUE(expr) OVER (
[ PARTITION BY partition_expression ]
[ ORDER BY order_list ]
)CREATE TABLE t1 (a int, b int);
INSERT INTO t1 VALUES(1,10),(2,20);
DELETE FROM t1 WHERE a=1 AND last_value(@a:=a,@b:=b,1);
SELECT @a,@b;
+------+------+
| @a | @b |
+------+------+
| 1 | 10 |
+------+------+CREATE TABLE t1 (
pk int primary key,
a int,
b int,
c char(10),
d decimal(10, 3),
e real
);
INSERT INTO t1 VALUES
( 1, 0, 1, 'one', 0.1, 0.001),
( 2, 0, 2, 'two', 0.2, 0.002),
( 3, 0, 3, 'three', 0.3, 0.003),
( 4, 1, 2, 'three', 0.4, 0.004),
( 5, 1, 1, 'two', 0.5, 0.005),
( 6, 1, 1, 'one', 0.6, 0.006),
( 7, 2, NULL, 'n_one', 0.5, 0.007),
( 8, 2, 1, 'n_two', NULL, 0.008),
( 9, 2, 2, NULL, 0.7, 0.009),
(10, 2, 0, 'n_four', 0.8, 0.010),
(11, 2, 10, NULL, 0.9, NULL);
SELECT pk, FIRST_VALUE(pk) OVER (ORDER BY pk) AS first_asc,
LAST_VALUE(pk) OVER (ORDER BY pk) AS last_asc,
FIRST_VALUE(pk) OVER (ORDER BY pk DESC) AS first_desc,
LAST_VALUE(pk) OVER (ORDER BY pk DESC) AS last_desc
FROM t1
ORDER BY pk DESC;
+----+-----------+----------+------------+-----------+
| pk | first_asc | last_asc | first_desc | last_desc |
+----+-----------+----------+------------+-----------+
| 11 | 1 | 11 | 11 | 11 |
| 10 | 1 | 10 | 11 | 10 |
| 9 | 1 | 9 | 11 | 9 |
| 8 | 1 | 8 | 11 | 8 |
| 7 | 1 | 7 | 11 | 7 |
| 6 | 1 | 6 | 11 | 6 |
| 5 | 1 | 5 | 11 | 5 |
| 4 | 1 | 4 | 11 | 4 |
| 3 | 1 | 3 | 11 | 3 |
| 2 | 1 | 2 | 11 | 2 |
| 1 | 1 | 1 | 11 | 1 |
+----+-----------+----------+------------+-----------+CREATE OR REPLACE TABLE t1 (i int);
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
SELECT i,
FIRST_VALUE(i) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW and 1 FOLLOWING) AS f_1f,
LAST_VALUE(i) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW and 1 FOLLOWING) AS l_1f,
FIRST_VALUE(i) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS f_1p1f,
LAST_VALUE(i) OVER (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS l_1p1f,
FIRST_VALUE(i) OVER (ORDER BY i ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) AS f_2p1p,
LAST_VALUE(i) OVER (ORDER BY i ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) AS l_2p1p,
FIRST_VALUE(i) OVER (ORDER BY i ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) AS f_1f2f,
LAST_VALUE(i) OVER (ORDER BY i ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) AS l_1f2f
FROM t1;
+------+------+------+--------+--------+--------+--------+--------+--------+
| i | f_1f | l_1f | f_1p1f | l_1p1f | f_2p1p | l_2p1p | f_1f2f | l_1f2f |
+------+------+------+--------+--------+--------+--------+--------+--------+
| 1 | 1 | 2 | 1 | 2 | NULL | NULL | 2 | 3 |
| 2 | 2 | 3 | 1 | 3 | 1 | 1 | 3 | 4 |
| 3 | 3 | 4 | 2 | 4 | 1 | 2 | 4 | 5 |
| 4 | 4 | 5 | 3 | 5 | 2 | 3 | 5 | 6 |
| 5 | 5 | 6 | 4 | 6 | 3 | 4 | 6 | 7 |
| 6 | 6 | 7 | 5 | 7 | 4 | 5 | 7 | 8 |
| 7 | 7 | 8 | 6 | 8 | 5 | 6 | 8 | 9 |
| 8 | 8 | 9 | 7 | 9 | 6 | 7 | 9 | 10 |
| 9 | 9 | 10 | 8 | 10 | 7 | 8 | 10 | 10 |
| 10 | 10 | 10 | 9 | 10 | 8 | 9 | NULL | NULL |
+------+------+------+--------+--------+--------+--------+--------+--------+COLLATION(str)SELECT COLLATION('abc');
+-------------------+
| COLLATION('abc') |
+-------------------+
| latin1_swedish_ci |
+-------------------+
SELECT COLLATION(_utf8'abc');
+-----------------------+
| COLLATION(_utf8'abc') |
+-----------------------+
| utf8_general_ci |
+-----------------------+CREATE TABLE origin (
i INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
v INT UNSIGNED NOT NULL
);
INSERT INTO origin(v) VALUES
(1),(2),(3),(4),(5),(10),(20),
(30),(40),(50),(60),(70),(80),
(90),(100),(200),(400),(800);
SET histogram_size=10,histogram_type=SINGLE_PREC_HB;
ANALYZE TABLE origin PERSISTENT FOR ALL;
+-------------+---------+----------+-----------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------+---------+----------+-----------------------------------------+
| test.origin | analyze | status | Engine-independent statistics collected |
| test.origin | analyze | status | OK |
+-------------+---------+----------+-----------------------------------------+
SELECT db_name,table_name,column_name,hist_type,
hex(histogram),decode_histogram(hist_type,histogram)
FROM mysql.column_stats WHERE db_name='test' and table_name='origin';
+---------+------------+-------------+----------------+----------------------+-------------------------------------------------------------------+
| db_name | table_name | column_name | hist_type | hex(histogram) | decode_histogram(hist_type,histogram) |
+---------+------------+-------------+----------------+----------------------+-------------------------------------------------------------------+
| test | origin | i | SINGLE_PREC_HB | 0F2D3C5A7887A5C3D2F0 | 0.059,0.118,0.059,0.118,0.118,0.059,0.118,0.118,0.059,0.118,0.059 |
| test | origin | v | SINGLE_PREC_HB | 000001060C0F161C1F7F | 0.000,0.000,0.004,0.020,0.024,0.012,0.027,0.024,0.012,0.376,0.502 |
+---------+------------+-------------+----------------+----------------------+-------------------------------------------------------------------+
SET histogram_size=20,histogram_type=DOUBLE_PREC_HB;
ANALYZE TABLE origin PERSISTENT FOR ALL;
+-------------+---------+----------+-----------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------+---------+----------+-----------------------------------------+
| test.origin | analyze | status | Engine-independent statistics collected |
| test.origin | analyze | status | OK |
+-------------+---------+----------+-----------------------------------------+
SELECT db_name,table_name,column_name,
hist_type,hex(histogram),decode_histogram(hist_type,histogram)
FROM mysql.column_stats WHERE db_name='test' and table_name='origin';
+---------+------------+-------------+----------------+------------------------------------------+-----------------------------------------------------------------------------------------+
| db_name | table_name | column_name | hist_type | hex(histogram) | decode_histogram(hist_type,histogram) |
+---------+------------+-------------+----------------+------------------------------------------+-----------------------------------------------------------------------------------------+
| test | origin | i | DOUBLE_PREC_HB | 0F0F2D2D3C3C5A5A78788787A5A5C3C3D2D2F0F0 | 0.05882,0.11765,0.05882,0.11765,0.11765,0.05882,0.11765,0.11765,0.05882,0.11765,0.05882 |
| test | origin | v | DOUBLE_PREC_HB | 5200F600480116067E0CB30F1B16831CB81FD67F | 0.00125,0.00250,0.00125,0.01877,0.02502,0.01253,0.02502,0.02502,0.01253,0.37546,0.50063 |IS_IPV4_MAPPED(expr)SELECT IS_IPV4_MAPPED(INET6_ATON('::10.0.1.1'));
+------------------------------------------+
| IS_IPV4_MAPPED(INET6_ATON('::10.0.1.1')) |
+------------------------------------------+
| 0 |
+------------------------------------------+
SELECT IS_IPV4_MAPPED(INET6_ATON('::ffff:10.0.1.1'));
+-----------------------------------------------+
| IS_IPV4_MAPPED(INET6_ATON('::ffff:10.0.1.1')) |
+-----------------------------------------------+
| 1 |
+-----------------------------------------------+SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| NULL |
+------------+
USE test;
Database changed
SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test |
+------------+VALUE(col_name)INSERT INTO t (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUE(a)+VALUE(b);VERSION()SELECT VERSION();
+----------------+
| VERSION() |
+----------------+
| 10.4.7-MariaDB |
+----------------+SELECT INET6_NTOA(UNHEX('0A000101'));
+-------------------------------+
| INET6_NTOA(UNHEX('0A000101')) |
+-------------------------------+
| 10.0.1.1 |
+-------------------------------+
SELECT INET6_NTOA(UNHEX('48F3000000000000D4321431BA23846F'));
+-------------------------------------------------------+
| INET6_NTOA(UNHEX('48F3000000000000D4321431BA23846F')) |
+-------------------------------------------------------+
| 48f3::d432:1431:ba23:846f |
+-------------------------------------------------------+SELECT MD5('testing');
+----------------------------------+
| MD5('testing') |
+----------------------------------+
| ae2b1fca515949e5d54fb22b8ed95575 |
+----------------------------------+INET_NTOA(expr)SELECT INET_NTOA(3232235777);
+-----------------------+
| INET_NTOA(3232235777) |
+-----------------------+
| 192.168.1.1 |
+-----------------------+SELECT FORMAT_BYTES(1000)FORMAT_BYTES(1024);
+--------------------+--------------------+
| FORMAT_BYTES(1000) | FORMAT_BYTES(1024) |
+--------------------+--------------------+
| 1000 bytes | 1.00 KiB |
+--------------------+--------------------+
SELECT FORMAT_BYTES(1000000),FORMAT_BYTES(1048576);
+-----------------------+-----------------------+
| FORMAT_BYTES(1000000) | FORMAT_BYTES(1048576) |
+-----------------------+-----------------------+
| 976.56 KiB | 1.00 MiB |
+-----------------------+-----------------------+
SELECT FORMAT_BYTES(1000000000),FORMAT_BYTES(1073741874);
+--------------------------+--------------------------+
| FORMAT_BYTES(1000000000) | FORMAT_BYTES(1073741874) |
+--------------------------+--------------------------+
| 953.67 MiB | 1.00 GiB |
+--------------------------+--------------------------+
SELECT FORMAT_BYTES(1000000000000),FORMAT_BYTES(1099511627776);
+-----------------------------+-----------------------------+
| FORMAT_BYTES(1000000000000) | FORMAT_BYTES(1099511627776) |
+-----------------------------+-----------------------------+
| 931.32 GiB | 1.00 TiB |
+-----------------------------+-----------------------------+
SELECT FORMAT_BYTES(1000000000000000),FORMAT_BYTES(1125899906842624);
+--------------------------------+--------------------------------+
| FORMAT_BYTES(1000000000000000) | FORMAT_BYTES(1125899906842624) |
+--------------------------------+--------------------------------+
| 909.49 TiB | 1.00 PiB |
+--------------------------------+--------------------------------+In the default mode, DECODE decrypts the encrypted string crypt_str using pass_str as the password. crypt_str should be a string returned from ENCODE(). The resulting string will be the original string only if pass_str is the same.
In , DECODE compares expr to the search expressions, in order. If it finds a match, the corresponding result expression is returned. If no matches are found, the default expression is returned, or NULL if no default is provided.
NULL values are treated as equivalent.
DECODE_ORACLE is a synonym for the Oracle-mode version of the function, and is available in all modes.
NULL values are treated as equivalent:
This page is licensed: GPLv2, originally from fill_help_tables.sql
DECODE(crypt_str,pass_str)A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate computers that are not connected to each other.
UUID() results are intended to be unique, but cannot always be relied upon to be unpredictable and unguessable.
A UUID is a 128-bit number represented by a utf8 string of five hexadecimal numbers in aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee format:
The first three numbers are generated from a timestamp.
The fourth number preserves temporal uniqueness in case the timestamp value loses monotonicity (for example, due to daylight saving time).
The fifth number is an IEEE 802 node number that provides spatial uniqueness. A random number is substituted if the latter is not available (for example, because the host computer has no Ethernet card, or we do not know how to find the hardware address of an interface on your operating system). In this case, spatial uniqueness cannot be guaranteed. Nevertheless, a collision should have very low probability.
Currently, the MAC address of an interface is taken into account only on FreeBSD and Linux. On other operating systems, MariaDB uses a randomly generated 48-bit number.
Statements using the UUID() function are not safe for statement-based replication.
The function generates a UUIDv1 and the results are generated according to the "DCE 1.1:Remote Procedure Call" (Appendix A) CAE (Common Applications Environment) Specifications published by The Open Group in October 1997 (Document Number C706).
UUID_SHORT() - Return short (64 bit) Universal Unique Identifier
SYS_GUID - UUID without the - character for Oracle compatibility
(Support UUID v4 generation)
(Implement native UUID7 function)
This page is licensed: GPLv2, originally from fill_help_tables.sql
Returns a Universally Unique Identifier (UUID) version 7. To generate a version 1 UUID, see the UUID function. To generate a version 4 UUID, see UUID_v4.
A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate computers that are not connected to each other.
A UUID is a 128-bit number represented by a utf8 string of five hexadecimal numbers in aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee format.
Statements using the UUID_v7() function are not safe for statement-based replication.
UUID() v1
UUID_SHORT() - Return short (64 bit) Universal Unique Identifier
SYS_GUID - UUID without the - character for Oracle compatibility
(Support UUID v4 generation)
(Implement native UUID7 function)
This page is licensed: CC BY-SA / Gnu FDL
Count rows affected by the last statement. This function returns the number of rows inserted, updated, or deleted by the previous DML operation.
ROW_COUNT() returns the number of rows updated, inserted or deleted by the preceding statement. This is the same as the row count that the mariadb client displays and the value from the C API function.
Generally:
For statements which return a result set (such as , , or ), returns -1, even when the result set is empty. This is also true for administrative statements, such as .
For DML statements other than and for , returns the number of affected rows.
For DDL statements (including ) and for other statements which don't return any result set (such as , , or ), returns 0.
For , affected rows is by default the number of rows that were actually changed. If the CLIENT_FOUND_ROWS flag to is specified when connecting to mariadbd, affected rows is instead the number of rows matched by the WHERE clause.
For , deleted rows are also counted. So, if REPLACE deletes a row and adds a new row, ROW_COUNT() returns 2.
For , values returned are as follows:
0: an existing row is set to its current values, and the CLIENT_FOUND_ROWS is not set.
1: the values are inserted as a new row, or an existing row is set to its current values, and the CLIENT_FOUND_ROWS is set.
2: an existing row is updated with new values.
ROW_COUNT() does not take into account rows that are not directly deleted/updated by the last statement. This means that rows deleted by foreign keys or triggers are not counted.
Warning: You can use ROW_COUNT() with prepared statements, but you need to call it after EXECUTE, not after , because the row count for allocate prepare is always 0.
Warning: When used after a statement, this function returns the number of rows affected by the last statement in the procedure, not by the whole procedure.
Warning: After , ROW_COUNT() returns the number of the rows you tried to insert, not the number of the successful writes.
This information can also be found in the .
Statements using the ROW_COUNT() function are not .
Example with prepared statements:
This page is licensed: GPLv2, originally from
Return the default value for a column. This function retrieves the value assigned to a column if no explicit value is provided during insertion.
DEFAULT(col_name)Returns the default value for a table column. If the column has no default value (and is not NULLABLE - NULLABLE fields have a NULL default), an error is returned.
For integer columns using , 0 is returned.
When using DEFAULT as a value to set in an or statement, you can use the bare keyword DEFAULT without the parentheses and argument to refer to the column in context. You can only use DEFAULT as a bare keyword if you are using it alone without a surrounding expression or function.
Select only non-default values for a column:
Update values to be one greater than the default value:
When referring to the default value exactly in UPDATE or INSERT, you can omit the argument:
This page is licensed: GPLv2, originally from
Return the last automatically generated value. This function retrieves the most recent AUTO_INCREMENT value generated by an INSERT statement.
LAST_INSERT_ID() (no arguments) returns the first automatically generated value successfully inserted for an column as a result of the most recently executed INSERT
statement. The value of LAST_INSERT_ID() remains unchanged if no rows are successfully inserted.
If one gives an argument to LAST_INSERT_ID(), then it will return the value of the expression and
the next call to LAST_INSERT_ID() will return the same value. The value is also sent to the client
and can be accessed by the function.
For example, after inserting a row that generates an AUTO_INCREMENT value, you can get the value like this:
You can also use LAST_INSERT_ID() to delete the last inserted row:
If no rows were successfully inserted, LAST_INSERT_ID() returns 0.
You can also use for this purpose.
The value of LAST_INSERT_ID() will be consistent across all versions if all rows in the or statement were successful.
The currently executing statement does not affect the value of LAST_INSERT_ID(). Suppose that you generate an AUTO_INCREMENT value with one statement, and then refer to LAST_INSERT_ID() in a
multiple-row INSERT statement that inserts rows into a table with its own AUTO_INCREMENT column. The value of LAST_INSERT_ID() will remain stable in the second statement; its value for the second and later rows is not affected by the earlier row insertions. (However, if you mix references to LAST_INSERT_ID() and LAST_INSERT_ID(expr), the effect is undefined.)
If the previous statement returned an error, the value of LAST_INSERT_ID() is undefined. For transactional tables, if the statement is rolled back due to an error, the value of LAST_INSERT_ID() is left undefined. For manual , the value of LAST_INSERT_ID() is not restored to that before the transaction; it remains as it was at the point of the ROLLBACK.
Within the body of a stored routine (procedure or function) or a trigger, the value of LAST_INSERT_ID() changes the same way as for statements executed outside the body of these kinds of objects. The
effect of a stored routine or trigger upon the value of LAST_INSERT_ID() that is seen by following statements depends on the kind of routine:
If a executes statements that change the value of LAST_INSERT_ID(), the new value will be seen by statements that follow the procedure call.
For and that change the value, the value is restored when the function or trigger ends, so following statements will not see a changed value.
- an alternative to auto_increment
This page is licensed: GPLv2, originally from
Count rows found by the last SELECT statement. This function returns the total number of rows matching the query, disregarding any LIMIT clause.
A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include an option in the SELECT statement, and then invoke FOUND_ROWS() afterwards.
You can also use FOUND_ROWS() to obtain the number of rows returned by a SELECT which does not contain a LIMIT clause. In this case you don't need to use the SQL_CALC_FOUND_ROWS option. This can be useful for example in a .
Also, this function works with some other statements which return a result set, including , and . For you should use . It also works as a , or after executing a prepared statement.
Statements which don't return any results don't affect FOUND_ROWS() - the previous value will still be returned.
Warning: When used after a statement, this function returns the number of rows selected by the last query in the procedure, not by the whole procedure.
Statements using the FOUND_ROWS() function are not .
This page is licensed: GPLv2, originally from
Generate a random UUID (v4). This function returns a version 4 UUID, which is generated using random numbers.
It is possible to generate UUIDv4 and UUIDv7, in addition to UUIDv1.
Returns a Universally Unique Identifier (UUID) version 4. To generate a version 1 UUID, see the function. To generate a version 7 UUID, see .
A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate computers that are not connected to each other.
UUID_v4() results are intended to be unique, but cannot always be relied upon to be unpredictable and unguessable.
A UUID is a 128-bit number represented by a utf8 string of five hexadecimal numbers in aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee format.
Statements using the UUID_v4() function are not .
v1
- Return short (64 bit) Universal Unique Identifier
- UUID without the - character for Oracle compatibility
This page is licensed: CC BY-SA / Gnu FDL
Generate a short 64-bit UUID. This function returns a unique, monotonically increasing integer suitable for use as a compact identifier.
Returns a "short" universally unique identifier as a 64-bit unsigned integer (rather than a string-form 128-bit identifier as returned by the UUID() function).
The value of UUID_SHORT() is guaranteed to be unique if the following conditions hold:
The server_id of the current host is unique among your set of master and replica servers.
server_id is between 0 and 255.
You don't set back your system time for your server between mariadbd restarts.
You do not invoke UUID_SHORT()
The UUID_SHORT() return value is constructed this way:
Statements using the UUID_SHORT() function are not .
; Return full (128 bit) Universally Unique Identifier
- an alternative to auto_increment available from
- UUID without the - character for Oracle compatibility
This page is licensed: GPLv2, originally from
Generate a UUID without hyphens. This Oracle-compatible function returns a globally unique identifier as a 32-character hexadecimal string.
The SYS_GUID function was introduced to enhance Oracle compatibility. Similar functionality can be achieved with the UUID function.
Returns a 16-byte globally unique identifier (GUID), similar to the function, but without the - character.
This page is licensed: CC BY-SA / Gnu FDL
Release all named locks held by the session. This function frees all user-level locks acquired with GET_LOCK() in the current connection.
Check if an IPv6 address is IPv4-compatible. This function returns 1 if the binary IPv6 address represents an IPv4-compatible address.
IS_IPV4_COMPAT(expr)Returns 1 if a given numeric binary string IPv6 address, such as returned by INET6_ATON(), is IPv4-compatible, otherwise returns 0.
When the argument is not , automatic implicit to INET6 is applied. As a consequence, IS_IPV4_COMPAT now understands arguments in both text representation and binary(16) representation.
The function understands only binary(16) representation.
This page is licensed: CC BY-SA / Gnu FDL
Convert an IPv6 address to binary. This function takes an IPv6 (or IPv4) address string and returns it as a VARBINARY(16) string.
Given an IPv6 or IPv4 network address as a string, returns a binary string that represents the numeric value of the address.
No trailing zone ID's or traling network masks are permitted. For IPv4 addresses, or IPv6 addresses with IPv4 address parts, no classful addresses or trailing port numbers are permitted and octal numbers are not supported.
The returned binary string will be or for IPv6 and IPv4 addresses respectively.
Returns NULL if the argument is not understood.
MariaDB starting with
INET6_ATON can take as an argument.
INET6_ATON cannot take as an argument.
Data Type
This page is licensed: CC BY-SA / Gnu FDL
Wait for a replica to apply a GTID. This function blocks until the replica has executed transactions up to the specified Global Transaction ID.
DECODE(expr, search_expr, result_expr [, search_expr2, result_expr2 ...] [default_expr])DECODE_ORACLE(expr, search_expr, result_expr [, search_expr2, result_expr2 ...] [default_expr])SELECT DECODE_ORACLE(2+1,3*1,'found1',3*2,'found2','default');
+--------------------------------------------------------+
| DECODE_ORACLE(2+1,3*1,'found1',3*2,'found2','default') |
+--------------------------------------------------------+
| found1 |
+--------------------------------------------------------+
SELECT DECODE_ORACLE(2+4,3*1,'found1',3*2,'found2','default');
+--------------------------------------------------------+
| DECODE_ORACLE(2+4,3*1,'found1',3*2,'found2','default') |
+--------------------------------------------------------+
| found2 |
+--------------------------------------------------------+
SELECT DECODE_ORACLE(2+2,3*1,'found1',3*2,'found2','default');
+--------------------------------------------------------+
| DECODE_ORACLE(2+2,3*1,'found1',3*2,'found2','default') |
+--------------------------------------------------------+
| default |
+--------------------------------------------------------+SELECT DECODE_ORACLE(NULL,NULL,'Nulls are equivalent','Nulls are not equivalent');
+----------------------------------------------------------------------------+
| DECODE_ORACLE(NULL,NULL,'Nulls are equivalent','Nulls are not equivalent') |
+----------------------------------------------------------------------------+
| Nulls are equivalent |
+----------------------------------------------------------------------------+UUID()SELECT UUID();
+--------------------------------------+
| UUID() |
+--------------------------------------+
| cd41294a-afb0-11df-bc9b-00241dd75637 |
+--------------------------------------+UUID_v7()SELECT UUID(),UUID_v4(),UUID_v7()\G
*************************** 1. row ***************************
UUID(): 63ae8c92-799a-11ef-98b2-f859713e4be4
UUID_v4(): a2443495-1b94-415b-b6fa-fe8e79ba4812
UUID_v7(): 01921e85-f198-7490-9b89-7dd0d468543bselect hex(kdf('foo', 'bar', 'infa', 'hkdf'));
+----------------------------------------+
| hex(kdf('foo', 'bar', 'infa', 'hkdf')) |
+----------------------------------------+
| 612875F859CFB4EE0DFEFF9F2A18E836 |
+----------------------------------------+ROW_COUNT()LAST_INSERT_ID(), LAST_INSERT_ID(expr)FOUND_ROWS()UUID_v4()UUID_SHORT()RELEASE_ALL_LOCKS()INET6_ATON(expr)seq_no within all replication domains specified in the gtid-list; in other words, it waits until the replica has reached the specified GTID position.An optional second argument gives a timeout in seconds. If the timeout expires before the specified GTID position is reached, then the function returns -1. Passing NULL or a negative number for the timeout means no timeout, and the function will wait indefinitely.
If the wait completes without a timeout, 0 is returned. Passing NULL for the gtid-list makes the function return NULL immediately, without waiting.
The gtid-list may be the empty string, in which case MASTER_GTID_WAIT() returns immediately. If the gtid-list contains fewer domains than gtid_slave_pos, then only those domains are waited upon. If gtid-list contains a domain that is not present in @@gtid_slave_pos, then MASTER_GTID_WAIT() will wait until an event containing such domain_id arrives on the replica (or until timed out or killed).
MASTER_GTID_WAIT() can be useful to ensure that a replica has caught up to a master. Simply take the value of gtid_binlog_pos on the master, and use it in a MASTER_GTID_WAIT() call on the replica; when the call completes, the replica will have caught up with that master position.
MASTER_GTID_WAIT() can also be used in client applications together with the last_gtid session variable. This is useful in a read-scaleout replication setup, where the application writes to a single master but divides the reads out to a number of replica to distribute the load. In such a setup, there
is a risk that an application could first do an update on the master, and then a bit later do a read on a replica, and if the replica is not fast enough, the data read from the slave might not include the update just made, possibly confusing the application and/or the end-user. One way to avoid this is to request the value of last_gtid on the master just after the update. Then before doing the read on the replica, do a MASTER_GTID_WAIT() on the value obtained from the master; this will ensure that the read is not performed until the replica has replicated sufficiently far for the update to have become visible.
Note that MASTER_GTID_WAIT() can be used even if the replica is configured not to use GTID for connections (CHANGE MASTER TO master_use_gtid=no). This is because from MariaDB 10, GTIDs are always logged on the master server, and always recorded on the replica servers.
MASTER_GTID_WAIT() is global; it waits for any master connection to reach the specified GTID position. MASTER_POS_WAIT() works only against a specific connection. This also means that while MASTER_POS_WAIT() aborts if its master connection is terminated with STOP REPLICA or due to an error, MASTER_GTID_WAIT() continues to wait while replicas are stopped.
MASTER_GTID_WAIT() can take its timeout as a floating-point value, so a timeout in fractional seconds is supported, eg. MASTER_GTID_WAIT("0-1-100", 0.5). (The minimum wait is one microsecond, 0.000001 seconds).
MASTER_GTID_WAIT() allows one to specify a timeout of zero in order to do a non-blocking check to see if the replicas have progressed to a specific GTID position (MASTER_POS_WAIT() takes a zero timeout as meaning an infinite wait). To do an infinite MASTER_GTID_WAIT(), specify a negative timeout, or omit the timeout argument.
MASTER_GTID_WAIT() does not return the number of events executed since the wait started, nor does it return NULL if a replica thread is stopped. It always returns either 0 for successful wait completed, or -1 for timeout reached (or NULL if the specified gtid-pos is NULL).
Since MASTER_GTID_WAIT() looks only at the seq_no part of the GTIDs, not the server_id, care is needed if a replica becomes diverged from another server so that two different GTIDs with the same seq_no (in the same domain) arrive at the same server. This situation is in any case best avoided; setting gtid_strict_mode is recommended, as this will prevent any such out-of-order sequence numbers from ever being replicated on a replica.
This page is licensed: CC BY-SA / Gnu FDL
Calculates and returns a hashed password string from the plaintext password str. Returns an empty string if the argument is NULL.
The return value is a nonbinary string in the connection character set and collation, determined by the values of the character_set_connection and collation_connection system variables.
This is the function that is used for hashing MariaDB passwords for storage in the Password column of the user table (see privileges), usually used with the SET PASSWORD statement. It is not intended for use in other applications.
The function takes into account the authentication plugin where applicable (a CREATE USER or SET PASSWORD statement). For example, when used in conjunction with a user authenticated by the ed25519 plugin, the statement will create a longer hash:
The behavior of this function is affected by the value of the old_passwords system variable. If this is set to 1 (0 is default), MariaDB reverts to using the mysql_old_password authentication plugin by default for newly created users and passwords.
Password Validation Plugins - permits the setting of basic criteria for passwords
OLD_PASSWORD() - pre-MySQL 4.1 password function
This page is licensed: GPLv2, originally from fill_help_tables.sql
NULL if the named lock did not exist. The lock does not exist if it was never obtained by a call to GET_LOCK() or if it has previously been released.str is case insensitive. If str is an empty string or NULL, RELEASE_LOCK() returns NULL and does nothing.
Statements using the RELEASE_LOCK function are not safe for statement-based replication.
The DO statement is convenient to use with RELEASE_LOCK().
Connection1:
Connection 2:
Connection 1:
It is possible to hold the same lock recursively. This example is viewed using the metadata_lock_info plugin:
This page is licensed: GPLv2, originally from fill_help_tables.sql
SELECT i FROM t WHERE i != DEFAULT(i);UPDATE t SET i = DEFAULT(i)+1 WHERE i < 100;INSERT INTO t (i) VALUES (DEFAULT);
UPDATE t SET i = DEFAULT WHERE i < 100;CREATE OR REPLACE TABLE t (
i INT NOT NULL AUTO_INCREMENT,
j INT NOT NULL,
k INT DEFAULT 3,
l INT NOT NULL DEFAULT 4,
m INT,
PRIMARY KEY (i)
);
DESC t;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| i | int(11) | NO | PRI | NULL | auto_increment |
| j | int(11) | NO | | NULL | |
| k | int(11) | YES | | 3 | |
| l | int(11) | NO | | 4 | |
| m | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
INSERT INTO t (j) VALUES (1);
INSERT INTO t (j,m) VALUES (2,2);
INSERT INTO t (j,l,m) VALUES (3,3,3);
SELECT * FROM t;
+---+---+------+---+------+
| i | j | k | l | m |
+---+---+------+---+------+
| 1 | 1 | 3 | 4 | NULL |
| 2 | 2 | 3 | 4 | 2 |
| 3 | 3 | 3 | 3 | 3 |
+---+---+------+---+------+
SELECT DEFAULT(i), DEFAULT(k), DEFAULT (l), DEFAULT(m) FROM t;
+------------+------------+-------------+------------+
| DEFAULT(i) | DEFAULT(k) | DEFAULT (l) | DEFAULT(m) |
+------------+------------+-------------+------------+
| 0 | 3 | 4 | NULL |
| 0 | 3 | 4 | NULL |
| 0 | 3 | 4 | NULL |
+------------+------------+-------------+------------+
SELECT DEFAULT(i), DEFAULT(k), DEFAULT (l), DEFAULT(m), DEFAULT(j) FROM t;
ERROR 1364 (HY000): Field 'j' doesn't have a default value
SELECT * FROM t WHERE i = DEFAULT(i);
Empty set (0.001 sec)
SELECT * FROM t WHERE j = DEFAULT(j);
ERROR 1364 (HY000): Field 'j' doesn't have a default value
SELECT * FROM t WHERE k = DEFAULT(k);
+---+---+------+---+------+
| i | j | k | l | m |
+---+---+------+---+------+
| 1 | 1 | 3 | 4 | NULL |
| 2 | 2 | 3 | 4 | 2 |
| 3 | 3 | 3 | 3 | 3 |
+---+---+------+---+------+
SELECT * FROM t WHERE l = DEFAULT(l);
+---+---+------+---+------+
| i | j | k | l | m |
+---+---+------+---+------+
| 1 | 1 | 3 | 4 | NULL |
| 2 | 2 | 3 | 4 | 2 |
+---+---+------+---+------+
SELECT * FROM t WHERE m = DEFAULT(m);
Empty set (0.001 sec)
SELECT * FROM t WHERE m <=> DEFAULT(m);
+---+---+------+---+------+
| i | j | k | l | m |
+---+---+------+---+------+
| 1 | 1 | 3 | 4 | NULL |
+---+---+------+---+------+SHOW ENGINES\G
*************************** 1. row ***************************
Engine: CSV
Support: YES
Comment: Stores tables as CSV files
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: MRG_MyISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
...
*************************** 8. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
8 rows in set (0.000 sec)
SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 8 |
+--------------+SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;
...
SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 23 |
+--------------+SYS_GUID()SELECT SYS_GUID();
+----------------------------------+
| SYS_GUID() |
+----------------------------------+
| 2C574E45BA2811EBB265F859713E4BE4 |
+----------------------------------+SELECT IS_IPV4_COMPAT(INET6_ATON('::10.0.1.1'));
+------------------------------------------+
| IS_IPV4_COMPAT(INET6_ATON('::10.0.1.1')) |
+------------------------------------------+
| 1 |
+------------------------------------------+
SELECT IS_IPV4_COMPAT(INET6_ATON('::48f3::d432:1431:ba23:846f'));
+-----------------------------------------------------------+
| IS_IPV4_COMPAT(INET6_ATON('::48f3::d432:1431:ba23:846f')) |
+-----------------------------------------------------------+
| 0 |
+-----------------------------------------------------------+MASTER_GTID_WAIT(gtid-list[, timeout)PASSWORD(str)CREATE USER edtest@localhost IDENTIFIED VIA ed25519 USING PASSWORD('secret');
CREATE USER edtest2@localhost IDENTIFIED BY 'secret';
SELECT CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)) FROM mysql.global_priv
WHERE user LIKE 'edtest%'\G
*************************** 1. row ***************************
CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)): edtest@localhost => {
...
"plugin": "ed25519",
"authentication_string": "ZIgUREUg5PVgQ6LskhXmO+eZLS0nC8be6HPjYWR4YJY",
...
}
*************************** 2. row ***************************
CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)): edtest2@localhost => {
...
"plugin": "mysql_native_password",
"authentication_string": "*14E65567ABDB5135D0CFD9A70B3032C179A49EE7",
...
}SELECT PASSWORD('notagoodpwd');
+-------------------------------------------+
| PASSWORD('notagoodpwd') |
+-------------------------------------------+
| *3A70EE9FC6594F88CE9E959CD51C5A1C002DC937 |
+-------------------------------------------+SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');RELEASE_LOCK(str)SELECT GET_LOCK('lock1',10);
+----------------------+
| GET_LOCK('lock1',10) |
+----------------------+
| 1 |
+----------------------+SELECT GET_LOCK('lock2',10);
+----------------------+
| GET_LOCK('lock2',10) |
+----------------------+
| 1 |
+----------------------+SELECT RELEASE_LOCK('lock1'), RELEASE_LOCK('lock2'), RELEASE_LOCK('lock3');
+-----------------------+-----------------------+-----------------------+
| RELEASE_LOCK('lock1') | RELEASE_LOCK('lock2') | RELEASE_LOCK('lock3') |
+-----------------------+-----------------------+-----------------------+
| 1 | 0 | NULL |
+-----------------------+-----------------------+-----------------------+SELECT GET_LOCK('lock3',10);
+----------------------+
| GET_LOCK('lock3',10) |
+----------------------+
| 1 |
+----------------------+
SELECT GET_LOCK('lock3',10);
+----------------------+
| GET_LOCK('lock3',10) |
+----------------------+
| 1 |
+----------------------+
SELECT * FROM INFORMATION_SCHEMA.METADATA_LOCK_INFO;
+-----------+---------------------+---------------+-----------+--------------+------------+
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
+-----------+---------------------+---------------+-----------+--------------+------------+
| 46 | MDL_SHARED_NO_WRITE | NULL | User lock | lock3 | |
+-----------+---------------------+---------------+-----------+--------------+------------+
SELECT RELEASE_LOCK('lock3');
+-----------------------+
| RELEASE_LOCK('lock3') |
+-----------------------+
| 1 |
+-----------------------+
SELECT * FROM INFORMATION_SCHEMA.METADATA_LOCK_INFO;
+-----------+---------------------+---------------+-----------+--------------+------------+
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
+-----------+---------------------+---------------+-----------+--------------+------------+
| 46 | MDL_SHARED_NO_WRITE | NULL | User lock | lock3 | |
+-----------+---------------------+---------------+-----------+--------------+------------+
SELECT RELEASE_LOCK('lock3');
+-----------------------+
| RELEASE_LOCK('lock3') |
+-----------------------+
| 1 |
+-----------------------+
SELECT * FROM INFORMATION_SCHEMA.METADATA_LOCK_INFO;
Empty set (0.000 sec)MDEV-11339 (Support UUID v4 generation)
MDEV-32637 (Implement native UUID7 function)
Determine collation coercibility. This function returns a numeric value indicating the priority of the argument's collation in comparison operations.
COERCIBILITY(str)Returns the collation coercibility value of the string argument. Coercibility defines what will be converted to what in case of collation conflict, with an expression with higher coercibility being converted to the collation of an expression with lower coercibility.
This page is licensed: GPLv2, originally from
In one can just use ROWNUM, without the parentheses.
ROWNUM() returns the current number of accepted rows in the current context. It main purpose is to emulate the . For MariaDB native applications, we recommend the usage of , as it is easier to use and gives more predictable results than the usage of ROWNUM().
The main difference between using LIMIT andROWNUM() to limit the rows in the result is thatLIMIT works on the result set while ROWNUM works on the number of accepted rows (before any ORDER orGROUP BY clauses).
The following queries will return the same results:
While the following may return different results based on in which orders the rows are found:
The recommended way to use ROWNUM to limit the number of returned rows and get predictable results is to have the query in a subquery and test for ROWNUM() in the outer query:
ROWNUM() can be used in the following contexts:
Used in other contexts, ROWNUM() will return 0.
In many cases where ROWNUM() is used, MariaDB will use the same optimizations it uses with .
LIMIT optimization is possible when using ROWNUM in the following manner:
When one is in a top level WHERE clause comparing ROWNUM() with a numerical constant using any of the following expressions:
ROWNUM() < number.
ROWNUM() <= number.
In the above cases, LIMIT optimization can be done in the
following cases:
For the current subquery when the ROWNUM comparison is done on the top level:
For an inner subquery, when the upper level has only a ROWNUM() comparison in the WHERE clause:
When ROWNUM() is used anywhere in a query, the optimization to ignore ORDER BY in subqueries are disabled.
This was done to get the following common Oracle query to work as expected:
By default, MariaDB ignores any ORDER BY in subqueries both because the SQL standard defines results sets in subqueries to be un-ordered and because of performance reasons (especially when using views in subqueries). See "Wrong result with GROUP BY ... WITH ROLLUP" for a discussion of this topic.
While MariaDB tries to emulate Oracle's usage of ROWNUM() as closely as possible, there are cases where the result is different:
When the optimizer finds rows in a different order (because of different storage methods or optimization). This may also happen in Oracle if one adds or deletes an index, in which case the rows may be found in a different order.
Note that usage of ROWNUM() in functions or will use their own context, not the caller's context.
support Oracle syntax: rownum
This page is licensed: CC BY-SA / Gnu FDL
Convert an IPv4 address to an integer. This function takes a dotted-quad IP string (e.g., '1.2.3.4') and returns its numeric representation.
INET_ATON(expr)Given the dotted-quad representation of an IPv4 network address as a string, returns an integer that represents the numeric value of the address. Addresses may be 4- or 8-byte addresses.
Returns NULL if the argument is not understood.
This is calculated as follows: 192 x 2563 + 168 x 256 2 + 1 x 256 + 1.
This page is licensed: GPLv2, originally from
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 9 |
+------------------+DELETE FROM product WHERE id = LAST_INSERT_ID();CREATE TABLE t (
id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
f VARCHAR(1))
ENGINE = InnoDB;
INSERT INTO t(f) VALUES('a');
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 1 |
+------------------+
INSERT INTO t(f) VALUES('b');
INSERT INTO t(f) VALUES('c');
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 3 |
+------------------+
INSERT INTO t(f) VALUES('d'),('e');
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 4 |
+------------------+
SELECT * FROM t;
+----+------+
| id | f |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
SELECT LAST_INSERT_ID(12);
+--------------------+
| LAST_INSERT_ID(12) |
+--------------------+
| 12 |
+--------------------+
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 12 |
+------------------+
INSERT INTO t(f) VALUES('f');
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 6 |
+------------------+
SELECT * FROM t;
+----+------+
| id | f |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
| 6 | f |
+----+------+
SELECT LAST_INSERT_ID(12);
+--------------------+
| LAST_INSERT_ID(12) |
+--------------------+
| 12 |
+--------------------+
INSERT INTO t(f) VALUES('g');
SELECT * FROM t;
+----+------+
| id | f |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
| 6 | f |
| 7 | g |
+----+------+SELECT UUID(),UUID_v4(),UUID_v7()\G
*************************** 1. row ***************************
UUID(): 63ae8c92-799a-11ef-98b2-f859713e4be4
UUID_v4(): a2443495-1b94-415b-b6fa-fe8e79ba4812
UUID_v7(): 01921e85-f198-7490-9b89-7dd0d468543b
CREATE TABLE t1 (a INT PRIMARY KEY NOT NULL, u UUID DEFAULT UUID_v4(), UNIQUE KEY(u));(server_id & 255) << 56
+ (server_startup_time_in_seconds << 24)
+ incremented_variable++;SELECT UUID_SHORT();
+-------------------+
| UUID_SHORT() |
+-------------------+
| 21517162376069120 |
+-------------------+CREATE TABLE t1 (a BIGINT UNSIGNED DEFAULT(uuid_short()) PRIMARY KEY);
INSERT INTO t1 VALUES(),();
SELECT * FROM t1;
+-------------------+
| a |
+-------------------+
| 98113699159474176 |
| 98113699159474177 |
+-------------------+CREATE TABLE t (A INT);
INSERT INTO t VALUES(1),(2),(3);
SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| 3 |
+-------------+
DELETE FROM t WHERE A IN(1,2);
SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| 2 |
+-------------+SET @q = 'INSERT INTO t VALUES(1),(2),(3);';
PREPARE stmt FROM @q;
EXECUTE stmt;
Query OK, 3 rows affected (0.39 sec)
Records: 3 Duplicates: 0 Warnings: 0
SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| 3 |
+-------------+SELECT RELEASE_ALL_LOCKS();
+---------------------+
| RELEASE_ALL_LOCKS() |
+---------------------+
| 0 |
+---------------------+
SELECT GET_LOCK('lock1',10);
+----------------------+
| GET_LOCK('lock1',10) |
+----------------------+
| 1 |
+----------------------+
SELECT RELEASE_ALL_LOCKS();
+---------------------+
| RELEASE_ALL_LOCKS() |
+---------------------+
| 1 |
+---------------------+SELECT HEX(INET6_ATON('10.0.1.1'));
+-----------------------------+
| HEX(INET6_ATON('10.0.1.1')) |
+-----------------------------+
| 0A000101 |
+-----------------------------+
SELECT HEX(INET6_ATON('48f3::d432:1431:ba23:846f'));
+----------------------------------------------+
| HEX(INET6_ATON('48f3::d432:1431:ba23:846f')) |
+----------------------------------------------+
| 48F3000000000000D4321431BA23846F |
+----------------------------------------------+ROWNUM()4
Coercible
Literal string
5
Numeric
Numeric and temporal values
6
Ignorable
NULL or derived from NULL
0
Explicit
Value using a COLLATE clause
1
No collation
Concatenated strings using different collations
2
Implicit
A string data type column value, CAST to a string data type
3
System constant
DATABASE(), USER() return value
SELECT COERCIBILITY(_latin1 'abc' COLLATE latin1_swedish_ci);
+-----------------------------------------------+
| COERCIBILITY(_latin1 'abc' COLLATE latin1_swedish_ci) |
+-----------------------------------------------+
| 0 |
+-----------------------------------------------+
CREATE TABLE t (a VARCHAR(30) COLLATE uca1400_swedish_ai_ci, b VARCHAR(30) COLLATE uca1400_german2_ai_ci) CHARSET utf8mb4;
INSERT INTO t VALUES ('abc', 'def'); /* a 2 coercibility */
SELECT COERCIBILITY(CONCAT(a, b)) FROM t;
+----------------------------+
| COERCIBILITY(CONCAT(a, b)) |
+----------------------------+
| 1 |
+----------------------------+
SELECT COERCIBILITY(CAST(1 AS CHAR));
+-------------------------------+
| COERCIBILITY(CAST(1 AS CHAR)) |
+-------------------------------+
| 2 |
+-------------------------------+
SELECT COERCIBILITY(USER());
+----------------------+
| COERCIBILITY(USER()) |
+----------------------+
| 3 |
+----------------------+
SELECT COERCIBILITY('abc');
+---------------------+
| COERCIBILITY('abc') |
+---------------------+
| 4 |
+---------------------+
SELECT COERCIBILITY(1);
+-----------------+
| COERCIBILITY(1) |
+-----------------+
| 5 |
+-----------------+
SELECT COERCIBILITY(NULL);
+--------------------+
| COERCIBILITY(NULL) |
+--------------------+
| 6 |
+--------------------+SELECT INET_ATON('192.168.1.1');
+--------------------------+
| INET_ATON('192.168.1.1') |
+--------------------------+
| 3232235777 |
+--------------------------+ROWNUM() = 1ROWNUM() can be also be the right argument to the comparison function.
SELECT * FROM t1 LIMIT 10;
SELECT * FROM t1 WHERE ROWNUM() <= 10;SELECT * FROM t1 ORDER BY a LIMIT 10;
SELECT * FROM t1 ORDER BY a WHERE ROWNUM() <= 10;SELECT * FROM (SELECT * FROM t1 ORDER BY a) WHERE ROWNUM() <= 10;INSERT INTO t1 VALUES (1,ROWNUM()),(2,ROWNUM()),(3,ROWNUM());
INSERT INTO t1 VALUES (1),(2) RETURNING a, ROWNUM();
UPDATE t1 SET row_num_column=ROWNUM();
DELETE FROM t1 WHERE a < 10 AND ROWNUM() < 2;
LOAD DATA INFILE 'filename' INTO TABLE t1 fields terminated BY ','
lines terminated BY "\r\n" (a,b) SET c=ROWNUM();SELECT * FROM t1 WHERE ROWNUM() <= 2 AND t1.a > 0SELECT * FROM (SELECT * FROM t1) AS t WHERE ROWNUM() <= 2SELECT * FROM (SELECT * FROM t1 ORDER BY a DESC) AS t WHERE rownum() <= 2;timeout10NULLA lock is released with RELEASE_LOCK(), when the connection terminates (either normally or abnormally). A connection can hold multiple locks at the same time, so a lock that is no longer needed needs to be explicitly released.
The IS_FREE_LOCK function returns whether a specified lock is free or not, and the IS_USED_LOCK whether the function is in use or not.
Locks obtained with GET_LOCK() do not interact with transactions. That is, committing a transaction does not release any such locks obtained during the transaction.
It is also possible to recursively set the same lock. If a lock with the same name is set n times, it needs to be released n times as well.
str is case insensitive for GET_LOCK() and related functions. If str is an empty string or NULL, GET_LOCK() returns NULL and does nothing. timeout supports microseconds.
If the metadata_lock_info plugin is installed, locks acquired with this function are visible in the Information Schema METADATA_LOCK_INFO table.
This function can be used to implement application locks or to simulate record locks. Names are locked on a server-wide basis. If a name has been locked by one client, GET_LOCK() blocks any request by another client for a lock with the same name. This allows clients that agree on a given lock name to use the name to perform cooperative advisory locking. But be aware that it also allows a client that is not among the set of cooperating clients to lock a name, either inadvertently or deliberately, and thus prevent any of the cooperating clients from locking that name. One way to reduce the likelihood of this is to use lock names that are database-specific or application-specific. For example, use lock names of the form db_name.str or app_name.str.
Statements using the GET_LOCK function are not safe for statement-based replication.
The patch to permit multiple locks was contributed by Konstantin "Kostja" Osipov (MDEV-3917).
Multiple locks can be held:
It is possible to hold the same lock recursively. This example is viewed using the metadata_lock_info plugin:
Timeout example: Connection 1:
Connection 2:
After 10 seconds...
Deadlocks are automatically detected and resolved. Connection 1:
Connection 2:
Connection 1:
Connection 2:
This page is licensed: GPLv2, originally from fill_help_tables.sql
GET_LOCK(str,timeout)SELECT GET_LOCK('lock1',10);
+----------------------+
| GET_LOCK('lock1',10) |
+----------------------+
| 1 |
+----------------------+
SELECT IS_FREE_LOCK('lock1'), IS_USED_LOCK('lock1');
+-----------------------+-----------------------+
| IS_FREE_LOCK('lock1') | IS_USED_LOCK('lock1') |
+-----------------------+-----------------------+
| 0 | 46 |
+-----------------------+-----------------------+
SELECT IS_FREE_LOCK('lock2'), IS_USED_LOCK('lock2');
+-----------------------+-----------------------+
| IS_FREE_LOCK('lock2') | IS_USED_LOCK('lock2') |
+-----------------------+-----------------------+
| 1 | NULL |
+-----------------------+-----------------------+SELECT GET_LOCK('lock2',10);
+----------------------+
| GET_LOCK('lock2',10) |
+----------------------+
| 1 |
+----------------------+
SELECT IS_FREE_LOCK('lock1'), IS_FREE_LOCK('lock2');
+-----------------------+-----------------------+
| IS_FREE_LOCK('lock1') | IS_FREE_LOCK('lock2') |
+-----------------------+-----------------------+
| 0 | 0 |
+-----------------------+-----------------------+
SELECT RELEASE_LOCK('lock1'), RELEASE_LOCK('lock2');
+-----------------------+-----------------------+
| RELEASE_LOCK('lock1') | RELEASE_LOCK('lock2') |
+-----------------------+-----------------------+
| 1 | 1 |
+-----------------------+-----------------------+SELECT GET_LOCK('lock3',10);
+----------------------+
| GET_LOCK('lock3',10) |
+----------------------+
| 1 |
+----------------------+
SELECT GET_LOCK('lock3',10);
+----------------------+
| GET_LOCK('lock3',10) |
+----------------------+
| 1 |
+----------------------+
SELECT * FROM INFORMATION_SCHEMA.METADATA_LOCK_INFO;
+-----------+---------------------+---------------+-----------+--------------+------------+
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
+-----------+---------------------+---------------+-----------+--------------+------------+
| 46 | MDL_SHARED_NO_WRITE | NULL | User lock | lock3 | |
+-----------+---------------------+---------------+-----------+--------------+------------+
SELECT RELEASE_LOCK('lock3');
+-----------------------+
| RELEASE_LOCK('lock3') |
+-----------------------+
| 1 |
+-----------------------+
SELECT * FROM INFORMATION_SCHEMA.METADATA_LOCK_INFO;
+-----------+---------------------+---------------+-----------+--------------+------------+
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
+-----------+---------------------+---------------+-----------+--------------+------------+
| 46 | MDL_SHARED_NO_WRITE | NULL | User lock | lock3 | |
+-----------+---------------------+---------------+-----------+--------------+------------+
SELECT RELEASE_LOCK('lock3');
+-----------------------+
| RELEASE_LOCK('lock3') |
+-----------------------+
| 1 |
+-----------------------+
SELECT * FROM INFORMATION_SCHEMA.METADATA_LOCK_INFO;
Empty set (0.000 sec)SELECT GET_LOCK('lock4',10);
+----------------------+
| GET_LOCK('lock4',10) |
+----------------------+
| 1 |
+----------------------+SELECT GET_LOCK('lock4',10);+----------------------+
| GET_LOCK('lock4',10) |
+----------------------+
| 0 |
+----------------------+SELECT GET_LOCK('lock5',10);
+----------------------+
| GET_LOCK('lock5',10) |
+----------------------+
| 1 |
+----------------------+SELECT GET_LOCK('lock6',10);
+----------------------+
| GET_LOCK('lock6',10) |
+----------------------+
| 1 |
+----------------------+SELECT GET_LOCK('lock6',10);
+----------------------+
| GET_LOCK('lock6',10) |
+----------------------+
| 0 |
+----------------------+SELECT GET_LOCK('lock5',10);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction