Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Retrieve system and session metadata. This section details functions to access database names, user details, version info, and query statistics like row counts.
Execute an expression repeatedly. This function runs a scalar expression a specified number of times, primarily for measuring query execution speed.
BENCHMARK(count,expr)The BENCHMARK() function executes the expression expr repeatedly count times. It may be used to time how quickly MariaDB processes the expression. The result value is always 0. The intended use is from within the , which reports query execution times.
This page is licensed: GPLv2, originally from
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.
SHOW BINLOG EVENTS - Show events and their positions in the binary log
This page is licensed: CC BY-SA / Gnu FDL
BINLOG_GTID_POS(binlog_filename,binlog_offset)SELECT BENCHMARK(1000000,ENCODE('hello','goodbye'));
+----------------------------------------------+
| BENCHMARK(1000000,ENCODE('hello','goodbye')) |
+----------------------------------------------+
| 0 |
+----------------------------------------------+
1 row in set (0.21 sec)SELECT BINLOG_GTID_POS("master-bin.000001", 600);Return the collation of a string. This function outputs the name of the collation rule used for sorting and comparing the string argument.
COLLATION(str)Returns the collation of the string argument. If str is not a string, it is considered as a binary string (so the function returns 'binary'). This applies to NULL, too. The return value is a string in the utf8 .
See .
This page is licensed: GPLv2, originally from
user@hostCURRENT_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 USER().
Backward-compatible behavior can be restored by setting old mode to SESSION_USER_IS_USER.
This page is licensed: GPLv2, originally from fill_help_tables.sql
SCHEMA()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
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
Inspect histogram data. This function allows viewing the distribution statistics stored in a histogram for query optimization analysis.
DECODE_HISTOGRAM(hist_type,histogram)Returns a string of comma separated numeric values corresponding to a probability distribution represented by the histogram of type hist_type (SINGLE_PREC_HB or DOUBLE_PREC_HB). The hist_type and histogram would be commonly used from the .
See for details.
This page is licensed: CC BY-SA / Gnu FDL
Count rows found by the last SELECT statement. This function returns the total number of rows matching the query, disregarding any LIMIT clause.
SESSION_USER()SELECT COLLATION('abc');
+-------------------+
| COLLATION('abc') |
+-------------------+
| latin1_swedish_ci |
+-------------------+
SELECT COLLATION(_utf8'abc');
+-----------------------+
| COLLATION(_utf8'abc') |
+-----------------------+
| utf8_general_ci |
+-----------------------+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 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 stored procedure.
Also, this function works with some other statements which return a result set, including SHOW, DESC and HELP. For DELETE ... RETURNING you should use ROW_COUNT(). It also works as a prepared statement, 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 CALL 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 safe for statement-based replication.
This page is licensed: GPLv2, originally from fill_help_tables.sql
The value of USER() may differ from the value of CURRENT_USER(), which is the user used to authenticate the current client. CURRENT_ROLE() returns the currently active role.
SYSTEM_USER() is a synonym for USER().
SYSTEM_USER() and SESSION_USER are synonyms for USER().
Statements using the USER() function or one of its synonyms are not safe for statement level replication.
To select only the IP address, use SUBSTRING_INDEX(),
This page is licensed: GPLv2, originally from fill_help_tables.sql
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 ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]])SELECT col1, col2 FROM table1 PROCEDURE ANALYSE(10, 2000);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 |FOUND_ROWS()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 |
+--------------+USER()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 |
+----------------------------------+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 CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
| 3 |
+-----------------+ROWNUM() returns the current number of accepted rows in the current context. It main purpose is to emulate the ROWNUM pseudo column in Oracle. For MariaDB native applications, we recommend the usage of LIMIT, 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.
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.
ROWNUM() = 1ROWNUM() can be also be the right argument to the comparison function.
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 MDEV-3926 "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 stored procedures will use their own context, not the caller's context.
MDEV-24089 support Oracle syntax: rownum
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
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
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
ROWNUM()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;ROW_COUNT()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
CHARSET(str)SELECT CURRENT_ROLE;
+--------------+
| CURRENT_ROLE |
+--------------+
| NULL |
+--------------+
SET ROLE staff;
SELECT CURRENT_ROLE;
+--------------+
| CURRENT_ROLE |
+--------------+
| staff |
+--------------+shell> mysql --user="anonymous"
SELECT USER(),CURRENT_USER();
+---------------------+----------------+
| USER() | CURRENT_USER() |
+---------------------+----------------+
| anonymous@localhost | @localhost |
+---------------------+----------------+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 |
+------+------+------+--------+--------+--------+--------+--------+--------+SELECT CHARSET('abc');
+----------------+
| CHARSET('abc') |
+----------------+
| latin1 |
+----------------+
SELECT CHARSET(CONVERT('abc' USING utf8));
+------------------------------------+
| CHARSET(CONVERT('abc' USING utf8)) |
+------------------------------------+
| utf8 |
+------------------------------------+
SELECT CHARSET(USER());
+-----------------+
| CHARSET(USER()) |
+-----------------+
| utf8 |
+-----------------+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 |
+-------------+NULLABLENULLFor integer columns using AUTO_INCREMENT, 0 is returned.
When using DEFAULT as a value to set in an INSERT or UPDATE 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 fill_help_tables.sql
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()DEFAULT(col_name)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 |
+---+---+------+---+------+SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| NULL |
+------------+
USE test;
Database changed
SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test |
+------------+Return the server version. This function outputs a string indicating the version number and distribution of the MariaDB server.
Returns a string that indicates the MariaDB server version. The string uses the utf8 character set.
The VERSION() string may have one or more of the following suffixes:
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 .
You can fool these applications by setting the version string from the command line or the my.cnf files with .
This page is licensed: GPLv2, originally from
VERSION()-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.
SELECT VERSION();
+----------------+
| VERSION() |
+----------------+
| 10.4.7-MariaDB |
+----------------+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
LAST_INSERT_ID(), LAST_INSERT_ID(expr)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 |
+----+------+