Learn about the CREATE statement in MariaDB Server. This DDL command allows you to define new database objects, including databases, tables, indexes, views, and stored routines.
CREATE [OR REPLACE] {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'comment'
CREATE DATABASE
creates a database with the given name. To use this statement, you need the CREATE privilege for the database. CREATE SCHEMA
is a synonym for CREATE DATABASE
.
For valid identifiers to use as database names, see Identifier Names.
If the optional OR REPLACE
clause is used, it acts as a shortcut for:
DROP DATABASE IF EXISTS db_name;
CREATE DATABASE db_name ...;
When the IF NOT EXISTS
clause is used, MariaDB will return a warning instead of an error if the specified database already exists.
The maximum length of a comment is 1024 bytes. If the comment length exceeds this length, an error/warning code 4144 is thrown. The database comment is also added to the db.opt
file, as well as to the information_schema.schemata table.
Comments added for databases do not exist.
CREATE DATABASE db1;
Query OK, 1 row affected (0.18 sec)
CREATE DATABASE db1;
ERROR 1007 (HY000): Can't create database 'db1'; database exists
CREATE OR REPLACE DATABASE db1;
Query OK, 2 rows affected (0.00 sec)
CREATE DATABASE IF NOT EXISTS db1;
Query OK, 1 row affected, 1 warning (0.01 sec)
SHOW WARNINGS;
+-------+------+----------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------+
| Note | 1007 | Can't create database 'db1'; database exists |
+-------+------+----------------------------------------------+
Setting the character sets and collation. See Setting Character Sets and Collations for more details.
CREATE DATABASE czech_slovak_names
CHARACTER SET = 'keybcs2'
COLLATE = 'keybcs2_bin';
CREATE DATABASE presentations COMMENT 'Presentations for conferences';
This page is licensed: GPLv2, originally from fill_help_tables.sql
CREATE [OR REPLACE]
[DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO sql_statement;
schedule:
AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
This statement creates and schedules a new event. It requires the EVENT privilege for the schema in which the event is to be created.
The minimum requirements for a valid CREATE EVENT statement are as follows:
The keywords CREATE EVENT
plus an event name, which uniquely identifies the event in the current schema.
An ON SCHEDULE
clause, which determines when and how often the event executes.
A DO
clause, which contains the SQL statement to be executed by an event.
Here is an example of a minimal CREATE EVENT
statement:
CREATE EVENT myevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
UPDATE myschema.mytable SET mycol = mycol + 1;
The previous statement creates an event named myevent. This event executes once
— one hour following its creation
— by running an SQL statement that increments the value of the myschema.mytable
table's mycol
column by 1.
The event_name must be a valid MariaDB identifier with a maximum length of 64 characters. It may be delimited using back ticks, and may be qualified with the name of a database schema. An event is associated with both a MariaDB user (the definer) and a schema, and its name must be unique among names of events within that schema. In general, the rules governing event names are the same as those for names of stored routines. See Identifier Names.
If no schema is indicated as part of event_name, the default (current) schema is assumed.
For valid identifiers to use as event names, see Identifier Names.
The OR REPLACE
clause works like this: If the event already exists, instead of an error being returned, the existing event will be dropped and replaced by the newly defined event.
The OR REPLACE
clause is not available.
If the IF NOT EXISTS
clause is used, MariaDB will return a warning instead of an error if the event already exists. Cannot be used together with OR REPLACE.
The ON SCHEDULE
clause can be used to specify when the event must be triggered.
If you want to execute the event only once (one time event), you can use the AT
keyword, followed by a timestamp. If you use CURRENT_TIMESTAMP, the event acts as soon as it is created. As a convenience, you can add one or more intervals to that timestamp. You can also specify a timestamp in the past, so that the event is stored but not triggered, until you modify it via ALTER EVENT.
The following example shows how to create an event that will be triggered tomorrow at a certain time:
CREATE EVENT example
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY + INTERVAL 3 HOUR
DO something;
You can also specify that an event must be triggered at a regular interval (recurring event). In such cases, use the EVERY
clause followed by the interval.
If an event is recurring, you can specify when the first execution must happen via the STARTS
clause and a maximum time for the last execution via the ENDS
clause. STARTS
and ENDS
clauses are followed by a timestamp and, optionally, one or more intervals. The ENDS
clause can specify a timestamp in the past, so that the event is stored but not executed until you modify it via ALTER EVENT.
In the following example, next month a recurring event will be triggered hourly for a week:
CREATE EVENT example
ON SCHEDULE EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH + INTERVAL 1 WEEK
DO some_task;
Intervals consist of a quantity and a time unit. The time units are the same used for other statements and time functions, except that you can't use microseconds for events. For simple time units, like HOUR
or MINUTE
, the quantity is an integer number, for example '10 MINUTE'. For composite time units, like HOUR_MINUTE
or HOUR_SECOND
, the quantity must be a string with all involved simple values and their separators, for example '2:30' or '2:30:30'.
The ON COMPLETION
clause can be used to specify if the event must be deleted after its last execution (that is, after its AT
or ENDS
timestamp is past). By default, events are dropped when they are expired. To explicitly state that this is the desired behaviour, you can use ON COMPLETION NOT PRESERVE
. Instead, if you want the event to be preserved, you can use ON COMPLETION PRESERVE
.
In you specify ON COMPLETION NOT PRESERVE
, and you specify a timestamp in the past for AT
or ENDS
clause, the event will be immediately dropped. In such cases, you will get a Note 1558: "Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation".
Events are ENABLE
d by default. If you want to stop MariaDB from executing an event, you may specify DISABLE
. When it is ready to be activated, you may enable it using ALTER EVENT. Another option is DISABLE ON SLAVE
, which indicates that an event was created on a master and has been replicated to the slave, which is prevented from executing the event. If DISABLE ON SLAVE
is specifically set, the event will be disabled everywhere. It will not be executed on the master or the replicas.
The COMMENT
clause may be used to set a comment for the event. Maximum length for comments is 64 characters. The comment is a string, so it must be quoted. To see events comments, you can query the INFORMATION_SCHEMA.EVENTS table (the column is named EVENT_COMMENT
).
Minimal CREATE EVENT
statement:
CREATE EVENT myevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
UPDATE myschema.mytable SET mycol = mycol + 1;
An event that will be triggered tomorrow at a certain time:
CREATE EVENT example
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY + INTERVAL 3 HOUR
DO something;
Next month a recurring event will be triggered hourly for a week:
CREATE EVENT example
ON SCHEDULE EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH + INTERVAL 1 WEEK
DO some_task;
OR REPLACE and IF NOT EXISTS:
CREATE EVENT myevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
UPDATE myschema.mytable SET mycol = mycol + 1;
ERROR 1537 (HY000): Event 'myevent' already exists
CREATE OR REPLACE EVENT myevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
UPDATE myschema.mytable SET mycol = mycol + 1;;
Query OK, 0 rows affected (0.00 sec)
CREATE EVENT IF NOT EXISTS myevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
UPDATE myschema.mytable SET mycol = mycol + 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
SHOW WARNINGS;
+-------+------+--------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------+
| Note | 1537 | Event 'myevent' already exists |
+-------+------+--------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
CREATE [OR REPLACE]
[DEFINER = {user | CURRENT_USER | role | CURRENT_ROLE }]
[AGGREGATE] FUNCTION [IF NOT EXISTS] func_name ([func_parameter[,...]])
RETURNS type
[characteristic ...]
RETURN func_body
func_parameter:
[ IN | OUT | INOUT | IN OUT ] param_name type
type:
Any valid MariaDB data type
characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
func_body:
Valid SQL procedure statement
Use the CREATE FUNCTION
statement to create a new stored function. You must have the CREATE ROUTINE database privilege to use CREATE FUNCTION
. A function takes any number of arguments and returns a value from the function body. The function body can be any valid SQL expression as you would use, for example, in any select expression. If you have the appropriate privileges, you can call the function exactly as you would any built-in function. See Security below for details on privileges.
You can also use a variant of the CREATE FUNCTION
statement to install a user-defined function (UDF) defined by a plugin. See CREATE FUNCTION (UDF) for details.
You can use a SELECT statement for the function body by enclosing it in parentheses, exactly as you would to use a subselect for any other expression. The SELECT
statement must return a single value. If more than one column is returned when the function is called, error 1241 results. If more than one row is returned when the function is called, error 1242 results. Use a LIMIT
clause to ensure only one row is returned.
You can also replace the RETURN
clause with a BEGIN...END compound statement. The compound statement must contain a RETURN
statement. When the function is called, the RETURN
statement immediately returns its result, and any statements after RETURN
are effectively ignored.
By default, a function is associated with the current database. To associate the function explicitly with a given database, specify the fully-qualified name as db_name.func_name
when you create it. If the function name is the same as the name of a built-in function, you must use the fully qualified name when you call it.
The parameter list enclosed within parentheses must always be present. If there are no parameters, an empty parameter list of () should be used. Parameter names are not case sensitive.
Each parameter can be declared to use any valid data type, except that the COLLATE attribute cannot be used.
For valid identifiers to use as function names, see Identifier Names.
OUT
, INOUT
and its equivalent IN OUT
, are only valid if called from SET
and not SELECT
. These quantifiers are especially useful for creating functions with more than one return value. This allows functions to be more complex and nested.
DELIMITER $$
CREATE FUNCTION add_func3(IN a INT, IN b INT, OUT c INT) RETURNS INT
BEGIN
SET c = 100;
RETURN a + b;
END;
$$
DELIMITER ;
SET @a = 2;
SET @b = 3;
SET @c = 0;
SET @res= add_func3(@a, @b, @c);
SELECT add_func3(@a, @b, @c);
ERROR 4186 (HY000): OUT or INOUT argument 3 for function add_func3 is not allowed here
DELIMITER $$
CREATE FUNCTION add_func4(IN a INT, IN b INT, d INT) RETURNS INT
BEGIN
DECLARE c, res INT;
SET res = add_func3(a, b, c) + d;
if (c > 99) then
return 3;
else
return res;
end if;
END;
$$
DELIMITER ;
SELECT add_func4(1,2,3);
+------------------+
| add_func4(1,2,3) |
+------------------+
| 3 |
+------------------+
Quantifiers are not available.
It is possible to create stored aggregate functions as well. See Stored Aggregate Functions for details.
The RETURNS
clause specifies the return type of the function. NULL
values are permitted with all return types.
What happens if the RETURN
clause returns a value of a different type? It depends on the SQL_MODE in effect at the moment of the function creation.
If the SQL_MODE is strict (STRICT_ALL_TABLES or STRICT_TRANS_TABLES flags are specified), a 1366 error will be produced.
Otherwise, the value is coerced to the proper type. For example, if a function specifies an ENUM
or SET
value in the RETURNS
clause, but the RETURN
clause returns an integer, the value returned from the function is the string for the corresponding ENUM
member of set of SET
members.
MariaDB stores the SQL_MODE system variable setting that is in effect at the time a routine is created, and always executes the routine with this setting in force, regardless of the server SQL mode in effect when the routine is invoked.
LANGUAGE SQL
is a standard SQL clause, and it can be used in MariaDB for portability. However that clause has no meaning, because SQL is the only supported language for stored functions.
A function is deterministic if it can produce only one result for a given list of parameters. If the result may be affected by stored data, server variables, random numbers or any value that is not explicitly passed, then the function is not deterministic. Also, a function is non-deterministic if it uses nondeterministic functions like NOW() or CURRENT_TIMESTAMP(). The optimizer may choose a faster execution plan if it known that the function is deterministic. In such cases, you should declare the routine using the DETERMINISTIC
keyword. If you want to explicitly state that the function is not deterministic (which is the default) you can use the NOT DETERMINISTIC
keywords.
If you declare a non-deterministic function as DETERMINISTIC
, you may get incorrect results. If you declare a deterministic function as NOT DETERMINISTIC
, in some cases the queries will be slower.
If the optional OR REPLACE
clause is used, it acts as a shortcut for:
DROP FUNCTION IF EXISTS function_name;
CREATE FUNCTION function_name ...;
with the exception that any existing privileges for the function are not dropped.
If the IF NOT EXISTS clause is used, MariaDB will return a warning instead of an error if the function already exists. Cannot be used together with OR REPLACE.
The [NOT] DETERMINISTIC
clause also affects binary logging, because the STATEMENT
format can not be used to store or replicate non-deterministic statements.
CONTAINS SQL
, NO SQL
, READS SQL DATA
, and MODIFIES SQL DATA
are informative clauses that tell the server what the function does. MariaDB does not check in any way whether the specified clause is correct. If none of these clauses are specified, CONTAINS SQL
is used by default.
MODIFIES SQL DATA
means that the function contains statements that may modify data stored in databases. This happens if the function contains statements like DELETE, UPDATE, INSERT, REPLACE or DDL.
READS SQL DATA
means that the function reads data stored in databases, but does not modify any data. This happens if SELECT statements are used, but there no write operations are executed.
CONTAINS SQL
means that the function contains at least one SQL statement, but it does not read or write any data stored in a database. Examples include SET or DO.
NO SQL
means nothing, because MariaDB does not currently support any language other than SQL.
A subset of Oracle's PL/SQL language is supported in addition to the traditional SQL/PSM-based MariaDB syntax. See Oracle mode for details on changes when running Oracle mode.
You must have the EXECUTE privilege on a function to call it. MariaDB automatically grants the EXECUTE
and ALTER ROUTINE
privileges to the account that called CREATE FUNCTION
, even if the DEFINER
clause was used.
Each function has an account associated as the definer. By default, the definer is the account
that created the function. Use the DEFINER
clause to specify a different account as the
definer. You must have the SUPER privilege, or, from MariaDB 10.5.2, the SET USER privilege, to use the DEFINER
clause. See Account Names for details on specifying accounts.
The SQL SECURITY
clause specifies what privileges are used when a function is called. If SQL SECURITY
is INVOKER
, the function body will be evaluated using the privileges of the user calling the function. If SQL SECURITY
is DEFINER
, the function body is always evaluated using the privileges of the definer account. DEFINER
is the default.
This allows you to create functions that grant limited access to certain data. For example, say you have a table that stores some employee information, and that you've granted SELECT
privileges only on certain columns to the user account roger
.
CREATE TABLE employees (name TINYTEXT, dept TINYTEXT, salary INT);
GRANT SELECT (name, dept) ON employees TO roger;
To allow the user the get the maximum salary for a department, define a function and grant the EXECUTE
privilege:
CREATE FUNCTION max_salary (dept TINYTEXT) RETURNS INT RETURN
(SELECT MAX(salary) FROM employees WHERE employees.dept = dept);
GRANT EXECUTE ON FUNCTION max_salary TO roger;
Since SQL SECURITY
defaults to DEFINER
, whenever the user roger
calls this function, the subselect will execute with your privileges. As long as you have privileges to select the salary of each employee, the caller of the function will be able to get the maximum salary for each department without being able to see individual salaries.
Function return types can be declared to use any valid character set and collation. If used, the COLLATE attribute needs to be preceded by a CHARACTER SET attribute.
If the character set and collation are not specifically set in the statement, the database defaults at the time of creation will be used. If the database defaults change at a later stage, the stored function character set/collation will not be changed at the same time; the stored function needs to be dropped and recreated to ensure the same character set/collation as the database is used.
The following example function takes a parameter, performs an operation using an SQL function, and returns the result.
CREATE FUNCTION hello (s CHAR(20))
RETURNS CHAR(50) DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!');
SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world! |
+----------------+
You can use a compound statement in a function to manipulate data with statements like INSERT
and UPDATE
. The following example creates a counter function that uses a temporary table to store the current value. Because the compound statement contains statements terminated with semicolons, you have to first change the statement delimiter with the DELIMITER
statement to allow the semicolon to be used in the function body. See Delimiters in the mariadb client for more.
CREATE TEMPORARY TABLE counter (c INT);
INSERT INTO counter VALUES (0);
DELIMITER //
CREATE FUNCTION counter () RETURNS INT
BEGIN
UPDATE counter SET c = c + 1;
RETURN (SELECT c FROM counter LIMIT 1);
END //
DELIMITER ;
Character set and collation:
CREATE FUNCTION hello2 (s CHAR(20))
RETURNS CHAR(50) CHARACTER SET 'utf8' COLLATE 'utf8_bin' DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!');
This page is licensed: GPLv2, originally from fill_help_tables.sql
CREATE [OR REPLACE] [UNIQUE|FULLTEXT|SPATIAL] INDEX
[IF NOT EXISTS] index_name
[index_type]
ON tbl_name (index_col_name,...)
[WAIT n | NOWAIT]
[index_option]
[algorithm_option | lock_option] ...
index_col_name:
col_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH | RTREE}
index_option:
[ KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
| CLUSTERING={YES| NO} ]
[ IGNORED | NOT IGNORED ]
algorithm_option:
ALGORITHM [=] {DEFAULT|INPLACE|COPY|NOCOPY|INSTANT}
lock_option:
LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
The CREATE INDEX
statement is used to add indexes to a table. Indexes can be created at the same as the table, with the CREATE TABLE statement. In some cases, such as for InnoDB primary keys, doing so during creation is preferable, as adding a primary key will involve rebuilding the table.
The statement is mapped to an ALTER TABLE
statement to create indexes. See ALTER TABLE. CREATE INDEX
cannot be used to create a PRIMARY KEY; use ALTER TABLE
instead.
If another connection is using the table, a metadata lock is active, and this statement will wait until the lock is released. This is also true for non-transactional tables.
Another shortcut, DROP INDEX, allows the removal of an index.
For valid identifiers to use as index names, see Identifier Names.
For limits on InnoDB indexes, see InnoDB Limitations.
Note that KEY_BLOCK_SIZE
is currently ignored in CREATE INDEX
, although it is included in the output of SHOW CREATE TABLE.
Executing the CREATE INDEX
statement requires the INDEX privilege for the table or the database.
Online DDL is supported with the ALGORITHM and LOCK clauses.
See InnoDB Online DDL Overview for more information on online DDL with InnoDB.
If the OR REPLACE
clause is used and if the index already exists, then instead of returning an error, the server will drop the existing index and replace it with the newly defined index.
If the IF NOT EXISTS
clause is used, then the index will only be created if an index with the same name does not already exist. If the index already exists, then a warning will be triggered by default.
See CREATE TABLE: Index Definitions for information about index definitions.
Set the lock wait timeout. See WAIT and NOWAIT.
See ALTER TABLE: ALGORITHM for more information.
See ALTER TABLE: LOCK for more information.
MariaDB provides progress reporting for CREATE INDEX
statement for clients that support the new progress reporting protocol. For example, if you were using the mariadb client, then the progress report might look like this::
CREATE INDEX i ON tab (num);
Stage: 1 of 2 'copy to tmp table' 46% of stage
The progress report is also shown in the output of the SHOW PROCESSLIST statement and in the contents of the information_schema.PROCESSLIST table.
See Progress Reporting for more information.
The WITHOUT OVERLAPS
clause allows you to constrain a primary or unique index such that application-time periods cannot overlap. It can be used like this:
CREATE UNIQUE INDEX u ON rooms (room_number, p WITHOUT OVERLAPS);
WITHOUT OVERLAPS
is not available.
Creating a unique index:
CREATE UNIQUE INDEX HomePhone ON Employees(Home_Phone);
OR REPLACE and IF NOT EXISTS:
CREATE INDEX xi ON xx5 (x);
Query OK, 0 rows affected (0.03 sec)
CREATE INDEX xi ON xx5 (x);
ERROR 1061 (42000): Duplicate key name 'xi'
CREATE OR REPLACE INDEX xi ON xx5 (x);
Query OK, 0 rows affected (0.03 sec)
CREATE INDEX IF NOT EXISTS xi ON xx5 (x);
Query OK, 0 rows affected, 1 warning (0.00 sec)
SHOW WARNINGS;
+-------+------+-------------------------+
| Level | Code | Message |
+-------+------+-------------------------+
| Note | 1061 | Duplicate key name 'xi' |
+-------+------+-------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
This page is licensed: CC BY-SA / Gnu FDL
The CREATE PACKAGE BODY
statement can be used in any mode.
The CREATE PACKAGE BODY
statement can be used in Oracle SQL_MODE.
In Oracle mode, the PL/SQL dialect is used, while in non-Oracle mode, SQL/PSM is used.
CREATE [ OR REPLACE ]
[DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
PACKAGE BODY
[ IF NOT EXISTS ]
[ db_name . ] package_name
[ package_characteristic... ]
{ AS | IS }
package_implementation_declare_section
package_implementation_executable_section
END [ package_name]
package_implementation_declare_section:
package_implementation_item_declaration
[ package_implementation_item_declaration... ]
[ package_implementation_routine_definition... ]
| package_implementation_routine_definition
[ package_implementation_routine_definition...]
package_implementation_item_declaration:
variable_declaration ;
variable_declaration:
variable_name[,...] type [:= expr ]
package_implementation_routine_definition:
FUNCTION package_specification_function
[ package_implementation_function_body ] ;
| PROCEDURE package_specification_procedure
[ package_implementation_procedure_body ] ;
package_implementation_function_body:
{ AS | IS } package_routine_body [func_name]
package_implementation_procedure_body:
{ AS | IS } package_routine_body [proc_name]
package_routine_body:
[ package_routine_declarations ]
BEGIN
statements [ EXCEPTION exception_handlers ]
END
package_routine_declarations:
package_routine_declaration ';' [package_routine_declaration ';']...
package_routine_declaration:
variable_declaration
| condition_name CONDITION FOR condition_value
| user_exception_name EXCEPTION
| CURSOR_SYM cursor_name
[ ( cursor_formal_parameters ) ]
IS select_statement
;
package_implementation_executable_section:
END
| BEGIN
statement ; [statement ; ]...
[EXCEPTION exception_handlers]
END
exception_handlers:
exception_handler [exception_handler...]
exception_handler:
WHEN_SYM condition_value [, condition_value]...
THEN_SYM statement ; [statement ;]...
condition_value:
condition_name
| user_exception_name
| SQLWARNING
| SQLEXCEPTION
| NOT FOUND
| OTHERS_SYM
| SQLSTATE [VALUE] sqlstate_value
| mariadb_error_code
The CREATE PACKAGE BODY
statement creates the package body for a stored package. The package specification must be previously created using the CREATE PACKAGE statement.
A package body provides implementations of the package public routines and can optionally have:
package-wide private variables
package private routines
forward declarations for private routines
an executable initialization section
SET sql_mode=ORACLE; # unnecessary from MariaDB 11.4
DELIMITER $$
CREATE OR REPLACE PACKAGE employee_tools AS
FUNCTION getSalary(eid INT) RETURN DECIMAL(10,2);
PROCEDURE raiseSalary(eid INT, amount DECIMAL(10,2));
PROCEDURE raiseSalaryStd(eid INT);
PROCEDURE hire(ename TEXT, esalary DECIMAL(10,2));
END;
$$
CREATE PACKAGE BODY employee_tools AS
-- package body variables
stdRaiseAmount DECIMAL(10,2):=500;
-- private routines
PROCEDURE log (eid INT, ecmnt TEXT) AS
BEGIN
INSERT INTO employee_log (id, cmnt) VALUES (eid, ecmnt);
END;
-- public routines
PROCEDURE hire(ename TEXT, esalary DECIMAL(10,2)) AS
eid INT;
BEGIN
INSERT INTO employee (name, salary) VALUES (ename, esalary);
eid:= last_insert_id();
log(eid, 'hire ' || ename);
END;
FUNCTION getSalary(eid INT) RETURN DECIMAL(10,2) AS
nSalary DECIMAL(10,2);
BEGIN
SELECT salary INTO nSalary FROM employee WHERE id=eid;
log(eid, 'getSalary id=' || eid || ' salary=' || nSalary);
RETURN nSalary;
END;
PROCEDURE raiseSalary(eid INT, amount DECIMAL(10,2)) AS
BEGIN
UPDATE employee SET salary=salary+amount WHERE id=eid;
log(eid, 'raiseSalary id=' || eid || ' amount=' || amount);
END;
PROCEDURE raiseSalaryStd(eid INT) AS
BEGIN
raiseSalary(eid, stdRaiseAmount);
log(eid, 'raiseSalaryStd id=' || eid);
END;
BEGIN
-- This code is executed when the current session
-- accesses any of the package routines for the first time
log(0, 'Session ' || connection_id() || ' ' || current_user || ' started');
END;
$$
DELIMITER ;
This page is licensed: CC BY-SA / Gnu FDL
The CREATE PACKAGE
statement can be used in any mode.
The CREATE PACKAGE
statement can be used when Oracle SQL_MODE is set.
In Oracle SQL mode, the PL/SQL dialect is used, while if Oracle mode is not set (the default), SQL/PSM is used.
CREATE
[ OR REPLACE]
[DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
PACKAGE [ IF NOT EXISTS ]
[ db_name . ] package_name
[ package_characteristic ... ]
{ AS | IS }
[ package_specification_element ... ]
END [ package_name ]
package_characteristic:
COMMENT 'string'
| SQL SECURITY { DEFINER | INVOKER }
package_specification_element:
FUNCTION_SYM package_specification_function ;
| PROCEDURE_SYM package_specification_procedure ;
package_specification_function:
func_name [ ( func_param [, func_param]... ) ]
RETURN type
[ package_routine_characteristic... ]
package_specification_procedure:
proc_name [ ( proc_param [, proc_param]... ) ]
[ package_routine_characteristic... ]
func_param:
param_name [ IN | OUT | INOUT | IN OUT ] type
proc_param:
param_name [ IN | OUT | INOUT | IN OUT ] type
type:
Any valid MariaDB explicit or anchored data type
package_routine_characteristic:
COMMENT 'string'
| LANGUAGE SQL
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
CREATE
[ OR REPLACE]
[DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
PACKAGE [ IF NOT EXISTS ]
[ db_name . ] package_name
[ package_characteristic ... ]
[ package_specification_element ... ]
END
package_characteristic:
COMMENT 'string'
| SQL SECURITY { DEFINER | INVOKER }
package_specification_element:
FUNCTION_SYM package_specification_function ;
| PROCEDURE_SYM package_specification_procedure ;
package_specification_function:
func_name [ ( func_param [, func_param]... ) ]
RETURNS type
[ package_routine_characteristic... ]
package_specification_procedure:
proc_name [ ( proc_param [, proc_param]... ) ]
[ package_routine_characteristic... ]
func_param:
param_name [ IN | OUT | INOUT | IN OUT ] type
proc_param:
param_name [ IN | OUT | INOUT | IN OUT ] type
type:
Any valid MariaDB explicit or anchored data type
package_routine_characteristic:
COMMENT 'string'
| LANGUAGE SQL
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
CREATE PACKAGE
creates the specification for a stored package (a collection of logically related stored objects). A stored package specification declares public routines (procedures and functions) of the package, but does not implement these routines.
A package whose specification was created by the CREATE PACKAGE
statement, should later be implemented using the CREATE PACKAGE BODY statement.
MariaDB starting with 10.8.0
The function parameter quantifiers for IN
, OUT
, INOUT
, and IN OUT
are supported anywhere.
The function parameter quantifiers for IN
, OUT
, INOUT
, and IN OUT
are supported only in procedures.
OUT
, INOUT
and its equivalent IN OUT
, are only valid if called from SET
and not SELECT
. These quantifiers are especially useful for creating functions and procedures with more than one return value. This allows functions and procedures to be more complex and nested.
SET sql_mode=ORACLE; # unnecessary from MariaDB 11.4
DELIMITER $$
CREATE OR REPLACE PACKAGE employee_tools AS
FUNCTION getSalary(eid INT) RETURN DECIMAL(10,2);
PROCEDURE raiseSalary(eid INT, amount DECIMAL(10,2));
PROCEDURE raiseSalaryStd(eid INT);
PROCEDURE hire(ename TEXT, esalary DECIMAL(10,2));
END;
$$
DELIMITER ;
This page is licensed: CC BY-SA / Gnu FDL
CREATE [OR REPLACE] SERVER [IF NOT EXISTS] server_name
FOREIGN DATA WRAPPER wrapper_name
OPTIONS (option [, option] ...)
option: <= MariaDB 11.6
{ HOST character-literal
| DATABASE character-literal
| USER character-literal
| PASSWORD character-literal
| SOCKET character-literal
| OWNER character-literal
| PORT numeric-literal }
option: >= MariaDB 11.7
{ HOST character-literal
| DATABASE character-literal
| USER character-literal
| PASSWORD character-literal
| SOCKET character-literal
| OWNER character-literal
| PORT numeric-literal
| PORT quoted-numerical-literal
| identifier character-literal}
This statement creates the definition of a server for use with the Spider, Connect, FEDERATED, or FederatedX storage engine. The CREATE SERVER
statement creates a new row in the servers table within the mysql database. This statement requires the FEDERATED ADMIN privilege.
The server_name should be a unique reference to the server. Server definitions are global within the scope of the server, it is not possible to qualify the server definition to a specific database. server_name has a maximum length of 64 characters (names longer than 64 characters are silently truncated), and is case-insensitive. You may specify the name as a quoted string.
The wrapper_name may be quoted with single quotes. Supported values are:
mysql
mariadb
(from MariaDB 10.3)
For each option you must specify either a character literal or numeric literal. Character literals are UTF-8, support a maximum length of 64 characters and default to a blank (empty) string. String literals are silently truncated to 64 characters. Numeric literals must be a number between 0 and 9999, default value is 0.
Note: The OWNER
option is currently not applied, and has no effect on the ownership or operation of the server connection that is created.
The CREATE SERVER statement creates an entry in themysql.servers table that can later be used with the CREATE TABLE statement when creating a Spider, Connect, FederatedX or FEDERATED table. The options that you specify will be used to populate the columns in the mysql.servers table. The table columns are Server_name, Host, Db, Username, Password, Port and Socket.
DROP SERVER removes a previously created server definition.
CREATE SERVER
is not written to the binary log, irrespective of the binary log format being used and therefore will not replicate.
Galera replicates the CREATE SERVER
, ALTER SERVER and DROP SERVER statements.
Galera does not replicate the CREATE SERVER
, ALTER SERVER and DROP SERVER statements.
For valid identifiers to use as server names, see Identifier Names.
The SHOW CREATE SERVER statement can be used to show the CREATE SERVER statement that created a given server definition.
The SHOW CREATE SERVER statement cannot be used to show the CREATE SERVER
statement that created a given server definition.
If the optional OR REPLACE
clause is used, it acts as a shortcut for:
DROP SERVER IF EXISTS name;
CREATE SERVER server_name ...;
If the IF NOT EXISTS clause is used, MariaDB will return a warning instead of an error if the server already exists. Cannot be used together with OR REPLACE.
CREATE SERVER s
FOREIGN DATA WRAPPER mariadb
OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
OR REPLACE and IF NOT EXISTS:
CREATE SERVER s
FOREIGN DATA WRAPPER mariadb
OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
ERROR 1476 (HY000): The foreign server, s, you are trying to create already exists
CREATE OR REPLACE SERVER s
FOREIGN DATA WRAPPER mariadb
OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
Query OK, 0 rows affected (0.00 sec)
CREATE SERVER IF NOT EXISTS s
FOREIGN DATA WRAPPER mariadb
OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
Query OK, 0 rows affected, 1 warning (0.00 sec)
SHOW WARNINGS;
+-------+------+----------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------+
| Note | 1476 | The foreign server, s, you are trying to create already exists |
+-------+------+----------------------------------------------------------------+
This page is licensed: GPLv2, originally from fill_help_tables.sql
CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(<a data-footnote-ref href="#user-content-fn-1">create_definition</a>,...) [<a data-footnote-ref href="#user-content-fn-2">table_options</a> ]... [<a data-footnote-ref href="#user-content-fn-3">partition_options</a>]
CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(<a data-footnote-ref href="#user-content-fn-1">create_definition</a>,...)] [<a data-footnote-ref href="#user-content-fn-2">table_options</a> ]... [<a data-footnote-ref href="#user-content-fn-3">partition_options</a>]
select_statement
CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_table_name | (LIKE old_table_name) }
select_statement:
[IGNORE | REPLACE] [AS] SELECT ... (Some legal select statement)
Use the CREATE TABLE
statement to create a table with the given name.
In its most basic form, the CREATE TABLE
statement provides a table name followed by a list of columns, indexes, and constraints. By default, the table is created in the default database. Specify a database with db_name.tbl_name
. If you quote the table name, you must quote the database name and table name separately as db_name`.`tbl_name
. This is particularly useful for CREATE TABLE ... SELECT, because it allows to create a table into a database, which contains data from other databases. See Identifier Qualifiers.
If a table with the same name exists, error 1050 results. Use IF NOT EXISTS to suppress this error and issue a note instead. Use SHOW WARNINGS to see notes.
The CREATE TABLE
statement automatically commits the current transaction, except when using the TEMPORARY keyword.
For valid identifiers to use as table names, see Identifier Names.
Microsecond precision can be between 0-6. If no precision is specified it is assumed to be 0, for backward compatibility reasons.
Executing the CREATE TABLE
statement requires the CREATE privilege for the table or the database.
If the OR REPLACE
clause is used and the table already exists, then instead of returning an error, the server will drop the existing table and replace it with the newly defined table.
This syntax was originally added to make replication more robust if it has to rollback and repeat statements such as CREATE ... SELECT
on replicas.
CREATE OR REPLACE TABLE table_name (a int);
is basically the same as:
DROP TABLE IF EXISTS table_name;
CREATE TABLE table_name (a int);
with the following exceptions:
If table_name
was locked with LOCK TABLES it will continue to be locked after the statement.
Temporary tables are only dropped if the TEMPORARY
keyword was used. (With DROP TABLE, temporary tables are preferred to be dropped before normal tables).
The table is dropped first (if it existed), after that the CREATE
is done. Because of this, if the CREATE
fails, then the table will not exist anymore after the statement. If the table was used with LOCK TABLES
it will be unlocked.
One can't use OR REPLACE
together with IF EXISTS
.
Replicas will by default use CREATE OR REPLACE
when replicating CREATE
statements that don''t use IF EXISTS
. This can be changed by setting the variable slave-ddl-exec-mode to STRICT
.
If the IF NOT EXISTS
clause is used, then the table will only be created if a table with the same name does not already exist. If the table already exists, then a warning will be triggered by default.
Use the TEMPORARY
keyword to create a temporary table that is only available to the current session. Temporary tables are dropped when the session ends. Temporary table names are specific to the session. They will not conflict with other temporary tables from other sessions even if they share the same name. They will shadow names of non-temporary tables or views, if they are identical. A temporary table can have the same name as a non-temporary table which is located in the same database. In that case, their name will reference the temporary table when used in SQL statements. You must have the CREATE TEMPORARY TABLES privilege on the database to create temporary tables. If no storage engine is specified, the default_tmp_storage_engine setting will determine the engine.
ROCKSDB temporary tables cannot be created by setting the default_tmp_storage_engine system variable, or using CREATE TEMPORARY TABLE LIKE
. If you try, an error is returned. Explicitly creating a temporary table with ENGINE=ROCKSDB
has never been permitted.
ROCKSDB temporary tables cannot be created by setting the default_tmp_storage_engine system variable, or using CREATE TEMPORARY TABLE LIKE
. They can be specified, but fail silently, and a MyISAM
table is created instead. Explicitly creating a temporary table with ENGINE=ROCKSDB
has never been permitted.
Use the LIKE
clause instead of a full table definition to create an empty table with the same definition as another table, including columns, indexes, and table options. Foreign key definitions, as well as any DATA DIRECTORY or INDEX DIRECTORY table options specified on the original table, will not be created.
LIKE
does not preserve the TEMPORARY
status of the original table. To make the new table TEMPORARY
as well, use CREATE TEMPORARY TABLE ... LIKE
.
LIKE
does not work with views, only base tables. Attempting to use it on a view will result in an error:
CREATE VIEW v (mycol) AS SELECT 'abc';
CREATE TABLE v2 LIKE v;
ERROR 1347 (HY000): 'test.v' is not of type 'BASE TABLE'
The same version of the table storage format as found in the original table is used for the new table.
CREATE TABLE ... LIKE
performs the same checks as CREATE TABLE
. So a statement may fail if a change in the SQL_MODE renders it invalid. For example:
CREATE OR REPLACE TABLE x (d DATE DEFAULT '0000-00-00');
SET SQL_MODE='NO_ZERO_DATE';
CREATE OR REPLACE TABLE y LIKE x;
ERROR 1067 (42000): Invalid default value for 'd'
You can create a table containing data from other tables using the CREATE ... SELECT
statement. Columns will be created in the table for each field returned by the SELECT
query.
You can also define some columns normally and add other columns from a SELECT
. You can also create columns in the normal way and assign them some values using the query, this is done to force a certain type or other field characteristics. The columns that are not named in the query will be placed before the others. For example:
CREATE TABLE test (a INT NOT NULL, b CHAR(10)) ENGINE=MyISAM
SELECT 5 AS b, c, d FROM another_table;
Remember that the query just returns data. If you want to use the same indexes, or the same columns attributes ([NOT] NULL
, DEFAULT
, AUTO_INCREMENT
) in the new table, you need to specify them manually. Types and sizes are not automatically preserved if no data returned by the SELECT
requires the full size, and VARCHAR
could be converted into CHAR
. The CAST() function can be used to forcee the new table to use certain types.
Aliases (AS
) are taken into account, and they should always be used when you SELECT
an expression (function, arithmetical operation, etc).
If an error occurs during the query, the table will not be created at all.
If the new table has a primary key or UNIQUE
indexes, you can use the IGNORE or REPLACE
keywords to handle duplicate key errors during the query. IGNORE
means that the newer values must not be inserted an identical value exists in the index. REPLACE
means that older values must be overwritten.
If the columns in the new table are more than the rows returned by the query, the columns populated by the query will be placed after other columns. Note that if the strict SQL_MODE
is on, and the columns that are not names in the query do not have a DEFAULT
value, an error will raise and no rows will be copied.
Concurrent inserts are not used during the execution of a CREATE ... SELECT
.
If the table already exists, an error similar to the following will be returned:
ERROR 1050 (42S01): Table 't' already exists
If the IF NOT EXISTS
clause is used and the table exists, a note will be produced instead of an error.
To insert rows from a query into an existing table, INSERT ... SELECT can be used.
create_definition:
{ col_name column_definition | | | CHECK (expr) }
column_definition:
[NOT NULL | NULL] [DEFAULT default_value | (expression)]
[ON UPDATE [NOW | CURRENT_TIMESTAMP] [(precision)]]
[AUTO_INCREMENT] [ZEROFILL] [UNIQUE [KEY] | [PRIMARY] KEY]
[INVISIBLE] [{WITH|WITHOUT} SYSTEM VERSIONING]
[COMMENT 'string'] [REF_SYSTEM_ID = value]
[]
| [GENERATED ALWAYS]
AS [ ROW {START|END} [NOT NULL ENABLE] [[PRIMARY] KEY]
| (expression) [VIRTUAL | PERSISTENT | STORED] ]
[INVISIBLE] [UNIQUE [KEY]] [COMMENT 'string']
constraint_definition:
CONSTRAINT [constraint_name] CHECK (expression)
MariaDB accepts the shortcut format with a REFERENCES
clause only in ALTER TABLE
and CREATE TABLE
statements, but that syntax does nothing. For example:
CREATE TABLE b(for_key INT REFERENCES a(not_key));
MariaDB will attempt to apply the constraint. See Foreign Keys examples.
MariaDB accepts the shortcut format with a REFERENCES
clause only in ALTER TABLE
and CREATE TABLE
statements, but that syntax does nothing. For example:
CREATE TABLE b(for_key INT REFERENCES a(not_key));
Each definition either creates a column in the table or specifies and index or constraint on one or more columns. See Indexes below for details on creating indexes.
Create a column by specifying a column name and a data type, optionally followed by column options. See Data Types for a full list of data types allowed in MariaDB.
Use the NULL
or NOT NULL
options to specify that values in the column may or may not be NULL
, respectively. By default, values may be NULL
. See also NULL Values in MariaDB.
Specify a default value using the DEFAULT
clause. If you don't specify DEFAULT
then the following rules apply:
If the column is not defined with NOT NULL
, AUTO_INCREMENT
or TIMESTAMP
, an explicit DEFAULT NULL
will be added.
Note that in MySQL, you may get an explicit DEFAULT
for primary key parts, if not specified with NOT NULL.
The default value will be used if you INSERT a row without specifying a value for that column, or if you specify DEFAULT for that column.
CURRENT_TIMESTAMP may also be used as the default value for a DATETIME
You can use most functions in DEFAULT
. Expressions should have parentheses around them. If you use a non deterministic function in DEFAULT
then all inserts to the table will be replicated in row mode. You can even refer to earlier columns in the DEFAULT
expression (excluding AUTO_INCREMENT
columns):
CREATE TABLE t1 (a int DEFAULT (1+1), b int DEFAULT (a+1));
CREATE TABLE t2 (a bigint primary key DEFAULT UUID_SHORT());
The DEFAULT
clause cannot contain any stored functions or subqueries, and a column used in the clause must already have been defined earlier in the statement.
It is possible to assign BLOB or TEXT columns a DEFAULT
value.
You can also use DEFAULT (NEXT VALUE FOR sequence).
Use AUTO_INCREMENT to create a column whose value can be set automatically from a simple counter. You can only use AUTO_INCREMENT
on a column with an integer type. The column must be a key, and there can only be one AUTO_INCREMENT
column in a table. If you insert a row without specifying a value for that column (or if you specify 0
, NULL
, or DEFAULT as the value), the actual value will be taken from the counter, with each insertion incrementing the counter by one. You can still insert a value explicitly. If you insert a value that is greater than the current counter value, the counter is
set based on the new value. An AUTO_INCREMENT
column is implicitly NOT NULL
. Use LAST_INSERT_ID to get the AUTO_INCREMENT value most recently used by an INSERT statement.
If the ZEROFILL
column option is specified for a column using a numeric data type, then the column will be set to UNSIGNED
and the spaces used by default to pad the field are replaced with zeros. ZEROFILL
is ignored in expressions or as part of a UNION. ZEROFILL
is a non-standard MySQL and MariaDB enhancement.
Use PRIMARY KEY
to make a column a primary key. A primary key is a special type of a unique key. There can be at most one primary key per table, and it is implicitly NOT NULL
.
Specifying a column as a unique key creates a unique index on that column. See the Index Definitions section below for more information.
Use UNIQUE KEY
(or just UNIQUE
) to specify that all values in the column must be distinct from each other. Unless the column is NOT NULL
, there may be multiple rows with NULL
in the column.
CREATE TABLE t_long_keys ( a INT PRIMARY KEY, b VARCHAR(4073), UNIQUE KEY `uk_b` (b) ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.022 sec)
show create table t_long_keys\G
*************************** 1. row ***************************
Table: t_long_keys
Create Table: CREATE TABLE `t_long_keys` (
`a` int(11) NOT NULL,
`b` varchar(4073) DEFAULT NULL,
PRIMARY KEY (`a`),
UNIQUE KEY `uk_b` (`b`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
1 row in set (0.001 sec)
select * from information_schema.INNODB_SYS_TABLES where name like '%t_long_keys%';;
+----------+----------------------+------+--------+-------+------------+---------------+------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+----------------------+------+--------+-------+------------+---------------+------------+
| 64 | securedb/t_long_keys | 33 | 5 | 43 | Dynamic | 0 | Single |
+----------+----------------------+------+--------+-------+------------+---------------+------------+
1 row in set (0.003 sec)
select * from information_schema.INNODB_SYS_COLUMNS where TABLE_ID=64;
+----------+---------------+-------+-------+--------+------+
| TABLE_ID | NAME | POS | MTYPE | PRTYPE | LEN |
+----------+---------------+-------+-------+--------+------+
| 64 | a | 0 | 6 | 1283 | 4 |
| 64 | b | 1 | 1 | 528399 | 4073 |
| 64 | DB_ROW_HASH_1 | 65538 | 6 | 9736 | 8 |
+----------+---------------+-------+-------+--------+------+
Specifying a column as a unique key creates a unique index on that column.
When any inserts or updates occur in the table, reading the binlog shows the hidden column (@3). it causes confusion for the user; we can document these behaviours.
### INSERT INTO `securedb`.`t_long_keys`
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='a' /* VARSTRING(4073) meta=4073 nullable=1 is_null=0 */
### @3=580 /* LONGINT meta=0 nullable=1 is_null=0 */
See the Index Definitions section below for more information.
You can provide a comment for each column using the COMMENT
clause. The maximum length is 1024 characters. Use the SHOW FULL COLUMNS statement to see column comments.
REF_SYSTEM_ID
can be used to specify Spatial Reference System IDs for spatial data type columns. For example:
CREATE TABLE t1(g GEOMETRY(9,4) REF_SYSTEM_ID=101);
A generated column is a column in a table that cannot explicitly be set to a specific value in a DML query. Instead, its value is automatically generated based on an expression. This expression might generate the value based on the values of other columns in the table, or it might generate the value by calling built-in functions or user-defined functions (UDFs).
There are two types of generated columns:
PERSISTENT
or STORED
: This type's value is actually stored in the table.
VIRTUAL
: This type's value is not stored at all. Instead, the value is generated dynamically when the table is queried. This type is the default.
Generated columns are also sometimes called computed columns or virtual columns.
For a complete description about generated columns and their limitations, see Generated (Virtual and Persistent/Stored) Columns.
Certain columns may be compressed. See Storage-Engine Independent Column Compression.
Columns may be made invisible, and hidden in certain contexts. See Invisible Columns.
Columns may be explicitly marked as included from system versioning. See System-versioned tables for details.
Columns may be explicitly marked as excluded from system versioning. See System-versioned tables for details.
index_definition:
{INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ...
{{{|}}} {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ...
{{{|}}} [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_option] ...
{{{|}}} [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ...
{{{|}}} [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition
index_col_name:
col_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH | RTREE}
index_option:
[ KEY_BLOCK_SIZE [=] value
{{{|}}} index_type
{{{|}}} WITH PARSER parser_name
{{{|}}} VISIBLE
{{{|}}} COMMENT 'string'
{{{|}}} CLUSTERING={YES| NO} ]
[ IGNORED | NOT IGNORED ]
reference_definition:
REFERENCES tbl_name (index_col_name,...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION
INDEX
and KEY
are synonyms.
Index names are optional, if not specified an automatic name will be assigned. Index name are needed to drop indexes and appear in error messages when a constraint is violated.
For limits on InnoDB indexes, see InnoDB Limitations.
Plain indexes are regular indexes that are not unique, and are not acting as a primary key or a foreign key. They are also not the "specialized" FULLTEXT
or SPATIAL
indexes.
See Getting Started with Indexes: Plain Indexes for more information.
For PRIMARY KEY
indexes, you can specify a name for the index, but it is ignored, and the name of the index is always PRIMARY
. A warning is explicitly issued if a name is specified. Before then, the name was silently ignored.
See Getting Started with Indexes: Primary Key for more information.
The UNIQUE
keyword means that the index will not accept duplicated values, except for NULLs. An error will raise if you try to insert duplicate values in a UNIQUE index.
For UNIQUE
indexes, you can specify a name for the constraint, using the CONSTRAINT
keyword. That name will be used in error messages.
-
See Getting Started with Indexes: Unique Index for more information.
For FOREIGN KEY
indexes, a reference definition must be provided.
For FOREIGN KEY
indexes, you can specify a name for the constraint, using the CONSTRAINT
keyword. That name will be used in error messages.
First, you have to specify the name of the target (parent) table and a column or a column list which must be indexed and whose values must match to the foreign key's values. The MATCH
clause is accepted to improve the compatibility with other DBMS's, but has no meaning in MariaDB. The ON DELETE
and ON UPDATE
clauses specify what must be done when a DELETE
(or a REPLACE
) statements attempts to delete a referenced row from the parent table, and when an UPDATE
statement attempts to modify the referenced foreign key columns in a parent table row, respectively. The following options are allowed:
RESTRICT
: The delete/update operation is not performed. The statement terminates with a 1451 error (SQLSTATE '2300').
NO ACTION
: Synonym for RESTRICT
.
CASCADE
: The delete/update operation is performed in both tables.
SET NULL
: The update or delete goes ahead in the parent table, and the corresponding foreign key fields in the child table are set to NULL
. (They must not be defined as NOT NULL
for this to succeed).
SET DEFAULT
: This option is currently implemented only for the PBXT storage engine, which is disabled by default and no longer maintained. It sets the child table's foreign key fields to their DEFAULT
values when the referenced parent table key entries are updated or deleted.
If either clause is omitted, the default behavior for the omitted clause is RESTRICT
.
See Foreign Keys for more information.
Use the FULLTEXT
keyword to create full-text indexes.
See Full-Text Indexes for more information.
Use the SPATIAL
keyword to create geometric indexes.
See SPATIAL INDEX for more information.
The KEY_BLOCK_SIZE
index option is similar to the KEY_BLOCK_SIZE table option.
With the InnoDB storage engine, if you specify a non-zero value for the KEY_BLOCK_SIZE
table option for the whole table, then the table will implicitly be created with the ROW_FORMAT table option set to COMPRESSED
. However, this does not happen if you just set the KEY_BLOCK_SIZE
index option for one or more indexes in the table. The InnoDB storage engine ignores the KEY_BLOCK_SIZE
index option. However, the SHOW CREATE TABLE statement may still report it for the index.
For information about the KEY_BLOCK_SIZE
index option, see the KEY_BLOCK_SIZE table option below.
Each storage engine supports some or all index types. See Storage Engine Index Types for details on permitted index types for each storage engine.
Different index types are optimized for different kind of operations:
BTREE
is the default type, and normally is the best choice. It is supported by all storage engines. It can be used to compare a column's value with a value using the =, >, >=, <, <=, BETWEEN
, and LIKE
operators. BTREE
can also be used to find NULL
values. Searches against an index prefix are possible.
HASH
is only supported by the MEMORY storage engine. HASH
indexes can only be used for =, <=, and >= comparisons. It can not be used for the ORDER BY
clause. Searches against an index prefix are not possible.
RTREE
is the default for SPATIAL indexes, but if the storage engine does not support it BTREE
can be used.
Index columns names are listed between parenthesis. After each column, a prefix length can be specified. If no length is specified, the whole column will be indexed. ASC
and DESC
can be specified. Individual columns in the index can be explicitly sorted in ascending or descending order. This can be useful for optimizing certain ORDER BY cases (MDEV-13756, MDEV-26938, MDEV-26939, MDEV-26996). Not only ascending, but also descending, indexes can be used to optimize MIN() and MAX() (MDEV-27576).
Index columns names are listed between parenthesis. After each column, a prefix length can be specified. If no length is specified, the whole column will be indexed. ASC
and DESC
can be specified. Prior to MariaDB 10.8, this was only for compatibility with other DBMSs, but had no meaning in MariaDB. From MariaDB 10.8, individual columns in the index can now be explicitly sorted in ascending or descending order. This can be useful for optimizing certain ORDER BY cases (MDEV-13756, MDEV-26938, MDEV-26939, MDEV-26996). From MariaDB 11.4.0, not only ascending, but also descending, indexes can now be used to optimize MIN() and MAX() (MDEV-27576).
The maximum number of parts in an index is 32.
The WITH PARSER
index option only applies to FULLTEXT indexes and contains the fulltext parser name. The fulltext parser must be an installed plugin.
Indexes can be declared visible. This is the default and it shows up in SHOW CREATE TABLE.
Indexes cannot be declared visible.
A comment of up to 1024 characters is permitted with the COMMENT
index option.
The COMMENT
index option allows you to specify a comment with user-readable text describing what the index is for. This information is not used by the server itself.
The CLUSTERING
index option is only valid for tables using the TokuDB storage engine.
Indexes can be specified to be ignored by the optimizer. See Ignored Indexes.
Indexes can be specified to be ignored by the optimizer. See Ignored Indexes.
period_definition:
PERIOD FOR [time_period_name | SYSTEM_TIME] (start_column_name, end_column_name)
MariaDB supports System-versioned tables, Application-time-period tables or Bitemporal Tables.
MariaDB introduced two ways to define a constraint:
CHECK(expression)
given as part of a column definition.
CONSTRAINT [constraint_name] CHECK (expression)
Before a row is inserted or updated, all constraints are evaluated in the order they are defined. If any constraints fails, then the row will not be updated. One can use most deterministic functions in a constraint, including UDFs.
CREATE TABLE t1 (a INT CHECK(a>0) ,b INT CHECK (b> 0), CONSTRAINT abc CHECK (a>b));
If you use the second format and you don't give a name to the constraint, then the constraint will get a auto generated name. This is done so that you can later delete the constraint with ALTER TABLE DROP constraint_name.
One can disable all constraint expression checks by setting the variable check_constraint_checks
to OFF
. This is useful for example when loading a table that violates some constraints that you want to later find and fix in SQL.
See CONSTRAINT for more information.
For each individual table you create (or alter), you can set some table options. The general syntax for setting options is:
<OPTION_NAME> = <option_value>, [<OPTION_NAME> = <option_value> ...]
The equal sign is optional.
Some options are supported by the server and can be used for all tables, no matter what storage engine they use; other options can be specified for all storage engines, but have a meaning only for some engines. Also, engines can extend CREATE TABLE with new options.
If the IGNORE_BAD_TABLE_OPTIONS
SQL_MODE is enabled, wrong table options generate a warning; otherwise, they generate an error.
table_option:
[STORAGE] ENGINE [=] engine_name
| AUTO_INCREMENT [=] number
| AVG_ROW_LENGTH [=] number
| [DEFAULT] CHARACTER SET [=] <a data-footnote-ref href="#user-content-fn-7">charset_name</a>
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] <a data-footnote-ref href="#user-content-fn-7">collation_name</a>
| COMMENT [=] 'string'
| CONNECTION [=] 'connect_string'
| DATA DIRECTORY [=] 'absolute path to directory'
| DELAY_KEY_WRITE [=] {0 | 1}
| ENCRYPTED [=] {YES | NO}
| ENCRYPTION_KEY_ID [=] number
| IETF_QUOTES [=] {YES | NO}
| INDEX DIRECTORY [=] 'absolute path to directory'
| INSERT_METHOD [=] { NO | FIRST | LAST }
| KEY_BLOCK_SIZE [=] number
| MAX_ROWS [=] number
| MIN_ROWS [=] number
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PAGE_CHECKSUM [=] {0 | 1}
| PAGE_COMPRESSED [=] {0 | 1}
| PAGE_COMPRESSION_LEVEL [=] {0 .. 9}
| PASSWORD [=] 'string'
| ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT|PAGE}
| SEQUENCE [=] {0|1}
| STATS_AUTO_RECALC [=] {DEFAULT|0|1}
| STATS_PERSISTENT [=] {DEFAULT|0|1}
| STATS_SAMPLE_PAGES [=] {DEFAULT|number}
| TABLESPACE tablespace_name
| TRANSACTIONAL [=] {0 | 1}
| UNION [=] (tbl_name[,tbl_name]...)
| WITH SYSTEM VERSIONING
[STORAGE] ENGINE
specifies a storage engine for the table. If this option is not used, the default storage engine is used instead. That is, the default_storage_engine session option value if it is set, or the value specified for the --default-storage-engine
mariadbd startup option, or the default storage engine, InnoDB. If the specified storage engine is not installed and active, the default value will be used, unless the NO_ENGINE_SUBSTITUTION
SQL MODE is set (default). This is only true for CREATE TABLE
, not for ALTER TABLE
. For a list of storage engines that are present in your server, issue a SHOW ENGINES.
AUTO_INCREMENT
specifies the initial value for the AUTO_INCREMENT primary key. This works for MyISAM, Aria, InnoDB, MEMORY, and ARCHIVE tables. You can change this option with ALTER TABLE
, but in that case the new value must be higher than the highest value which is present in the AUTO_INCREMENT
column. If the storage engine does not support this option, you can insert (and then delete) a row having the wanted value - 1 in the AUTO_INCREMENT
column.
AVG_ROW_LENGTH
is the average rows size. It only applies to tables using MyISAM and Aria storage engines that have the ROW_FORMAT table option set to FIXED
format.
MyISAM uses MAX_ROWS
and AVG_ROW_LENGTH
to decide the maximum size of a table (default: 256TB, or the maximum file size allowed by the system).
[DEFAULT] CHARACTER SET
(or [DEFAULT] CHARSET
) is used to set a default character set for the table. This is the character set used for all columns where an explicit character set is not specified. If this option is omitted or DEFAULT
is specified, the database's default character set will be used (except for the JSON data type, which is utf8mb4 by default). See Setting Character Sets and Collations for details on setting the character sets.
CHECKSUM
(or TABLE_CHECKSUM
) can be set to 1 to maintain a live checksum for all table's rows. This makes write operations slower, but CHECKSUM TABLE will be very fast. This option is only supported for MyISAM and Aria tables.
[DEFAULT] COLLATE
is used to set a default collation for the table. This is the collation used for all columns where an explicit character set is not specified. If this option is omitted or DEFAULT
is specified, the database's default option will be used (except for the JSON data type, which uses utf8mb4_bin by default). See Setting Character Sets and Collations for details on setting the collations
COMMENT
is a comment for the table. The maximum length is 2048 characters. Also used to define table parameters when creating a Spider table.
CONNECTION
is used to specify a server name or a connection string for a Spider, CONNECT, Federated or FederatedX table.
DATA DIRECTORY
and INDEX DIRECTORY
are supported for MyISAM and Aria, and DATA DIRECTORY is also supported by InnoDB if the innodb_file_per_table server system variable is enabled, but only in CREATE TABLE, not in ALTER TABLE. So, carefully choose a path for InnoDB tables at creation time, because it cannot be changed without dropping and re-creating the table. These options specify the paths for data files and index files, respectively. If these options are omitted, the database's directory will be used to store data files and index files. Note that these table options do not work for partitioned tables (use the partition options instead), or if the server has been invoked with the --skip-symbolic-links startup option. To avoid the overwriting of old files with the same name that could be present in the directories, you can use the --keep_files_on_create option (an error will be issued if files already exist). These options are ignored if the NO_DIR_IN_CREATE
SQL_MODE is enabled (useful for replicas). Also note that symbolic links cannot be used for InnoDB tables.
DATA DIRECTORY
works by creating symlinks from where the table would normally have been (inside the datadir) to where the option specifies. For security reasons, to avoid bypassing the privilege system, the server does not permit symlinks inside the datadir. Therefore, DATA DIRECTORY
cannot be used to specify a location inside the datadir. An attempt to do so will result in an error 1210 (HY000) Incorrect arguments to DATA DIRECTORY
.
DELAY_KEY_WRITE
is supported by MyISAM and Aria, and can be set to 1 to speed up write operations. In that case, when data are modified, the indexes are not updated until the table is closed. Writing the changes to the index file altogether can be much faster. However, note that this option is applied only if the delay_key_write
server variable is set to 'ON'. If it is 'OFF' the delayed index writes are always disabled, and if it is 'ALL' the delayed index writes are always used, disregarding the value of DELAY_KEY_WRITE
.
The ENCRYPTED
table option can be used to manually set the encryption status of an InnoDB table. See InnoDB Encryption for more information.
Aria does not support the ENCRYPTED
table option. See MDEV-18049.
See Data-at-Rest Encryption for more information.
The ENCRYPTION_KEY_ID
table option can be used to manually set the encryption key of an InnoDB table. See InnoDB Encryption for more information.
Aria does not support the ENCRYPTION_KEY_ID
table option. See MDEV-18049.
See Data-at-Rest Encryption for more information.
For the CSV storage engine, the IETF_QUOTES
option, when set to YES
, enables IETF-compatible parsing of embedded quote and comma characters. Enabling this option for a table improves compatibility with other tools that use CSV, but is not compatible with MySQL CSV tables, or MariaDB CSV tables created without this option. Disabled by default.
INSERT_METHOD
is only used with MERGE tables. This option determines in which underlying table the new rows should be inserted. If you set it to 'NO' (which is the default) no new rows can be added to the table (but you will still be able to perform INSERT
s directly against the underlying tables). FIRST
means that the rows are inserted into the first table, and LAST
means that thet are inserted into the last table.
KEY_BLOCK_SIZE
is used to determine the size of key blocks, in bytes or kilobytes. However, this value is just a hint, and the storage engine could modify or ignore it. If KEY_BLOCK_SIZE
is set to 0, the storage engine's default value will be used.
With the InnoDB storage engine, if you specify a non-zero value for the KEY_BLOCK_SIZE
table option for the whole table, then the table will implicitly be created with the ROW_FORMAT table option set to COMPRESSED
.
MIN_ROWS
and MAX_ROWS
let the storage engine know how many rows you are planning to store as a minimum and as a maximum. These values will not be used as real limits, but they help the storage engine to optimize the table. MIN_ROWS
is only used by MEMORY storage engine to decide the minimum memory that is always allocated. MAX_ROWS
is used to decide the minimum size for indexes.
PACK_KEYS
can be used to determine whether the indexes will be compressed. Set it to 1 to compress all keys. With a value of 0, compression will not be used. With the DEFAULT
value, only long strings will be compressed. Uncompressed keys are faster.
PAGE_CHECKSUM
is only applicable to Aria tables, and determines whether indexes and data should use page checksums for extra safety.
PAGE_COMPRESSED
is used to enable InnoDB page compression for InnoDB tables.
PAGE_COMPRESSION_LEVEL
is used to set the compression level for InnoDB page compression for InnoDB tables. The table must also have the PAGE_COMPRESSED table option set to 1
.
Valid values for PAGE_COMPRESSION_LEVEL
are 1 (the best speed) through 9 (the best compression), .
PASSWORD
is unused.
RAID_TYPE
is an obsolete option, as the raid support has been disabled since MySQL 5.0.
The ROW_FORMAT
table option specifies the row format for the data file. Possible values are engine-dependent.
For MyISAM, the supported row formats are:
FIXED
DYNAMIC
COMPRESSED
The COMPRESSED
row format can only be set by the myisampack command line tool.
See MyISAM Storage Formats for more information.
For Aria, the supported row formats are:
PAGE
FIXED
DYNAMIC
.
See Aria Storage Formats for more information.
For InnoDB, the supported row formats are:
COMPACT
REDUNDANT
COMPRESSED
DYNAMIC
.
If the ROW_FORMAT
table option is set to FIXED
for an InnoDB table, then the server will either return an error or a warning depending on the value of the innodb_strict_mode system variable. If the innodb_strict_mode system variable is set to OFF
, then a warning is issued, and MariaDB will create the table using the default row format for the specific MariaDB server version. If the innodb_strict_mode system variable is set to ON
, then an error will be raised.
See InnoDB Storage Formats for more information.
Other storage engines do not support the ROW_FORMAT
table option.
If the table is a sequence, then it will have the SEQUENCE
set to 1
.
STATS_AUTO_RECALC
indicates whether to automatically recalculate persistent statistics (see STATS_PERSISTENT
, below) for an InnoDB table.
If set to 1
, statistics will be recalculated when more than 10% of the data has changed. When set to 0
, stats will be recalculated only when an ANALYZE TABLE is run. If set to DEFAULT
, or left out, the value set by the innodb_stats_auto_recalc system variable applies. See InnoDB Persistent Statistics.
STATS_PERSISTENT
indicates whether the InnoDB statistics created by ANALYZE TABLE will remain on disk or not. It can be set to 1
(on disk), 0
(not on disk, the pre-MariaDB 10 behavior), or DEFAULT
(the same as leaving out the option), in which case the value set by the innodb_stats_persistent system variable will apply. Persistent statistics stored on disk allow the statistics to survive server restarts, and provide better query plan stability. See InnoDB Persistent Statistics.
STATS_SAMPLE_PAGES
indicates how many pages are used to sample index statistics. If 0 or DEFAULT, the default value, the innodb_stats_sample_pages value is used. See InnoDB Persistent Statistics.
TRANSACTIONAL
is only applicable for Aria tables. In future Aria tables created with this option will be fully transactional, but currently this provides a form of crash protection. See Aria Storage Engine for more details.
UNION
must be specified when you create a MERGE table. This option contains a comma-separated list of MyISAM tables which are accessed by the new table. The list is enclosed between parenthesis. Example: UNION = (t1,t2)
WITH SYSTEM VERSIONING
is used for creating System-versioned tables.
partition_options:
PARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY(column_list)
| RANGE(expr)
| LIST(expr)
| SYSTEM_TIME [INTERVAL time_quantity <a data-footnote-ref href="#user-content-fn-8">time_unit</a>] [LIMIT num] }
[PARTITIONS num]
[SUBPARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY(column_list) }
[SUBPARTITIONS num]
]
[(partition_definition [, partition_definition] ...)]
partition_definition:
[PARTITION] partition_name
[VALUES {LESS THAN {(expr) | MAXVALUE} | IN (value_list)}]
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'comment_text' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[NODEGROUP [=] node_group_id]
[(subpartition_definition [, subpartition_definition] ...)]
subpartition_definition:
SUBPARTITION logical_name
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'comment_text' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[NODEGROUP [=] node_group_id]
If the PARTITION BY
clause is used, the table will be partitioned. A partition method must be explicitly indicated for partitions and subpartitions. Partition methods are:
[LINEAR] HASH creates a hash key which will be used to read and write rows. The partition function can be any valid SQL expression which returns an INTEGER
number. Thus, it is possible to use the HASH method on an integer column, or on functions which accept integer columns as an argument. However, VALUES LESS THAN
and VALUES IN
clauses can not be used with HASH. An example:
CREATE TABLE t1 (a INT, b CHAR(5), c DATETIME)
PARTITION BY HASH ( YEAR(c) );
[LINEAR] HASH can be used for subpartitions, too.
RANGE partitions the rows using on a range of values, using the VALUES LESS THAN
operator. VALUES IN
is not allowed with RANGE
. The partition function can be any valid SQL expression which returns a single value.
LIST assigns partitions based on a table's column with a restricted set of possible values. It is similar to RANGE
, but VALUES IN
must be used for at least 1 columns, and VALUES LESS THAN
is disallowed.
SYSTEM_TIME
partitioning is used for System-versioned tables to store historical data separately from current data.
Only HASH and KEY can be used for subpartitions, and they can be [LINEAR]
.
It is possible to define up to 8092 partitions and subpartitions.
The number of defined partitions can be optionally specified as PARTITION count
. This can be done to avoid specifying all partitions individually. But you can also declare each individual partition and, additionally, specify a PARTITIONS count
clause; in the case, the number of PARTITION
s must equal count.
Also see Partitioning Types Overview.
The PARTITION
keyword is optional as part of the partition definition. Instead of this:
CREATE OR REPLACE TABLE t1 (x INT)
PARTITION BY RANGE(x) (
partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than (30),
partition p4 values less than (40),
partition p5 values less than (50),
partition pn values less than maxvalue);
The following can be used:
CREATE OR REPLACE TABLE t1 (x INT)
PARTITION BY RANGE(x) (
p1 values less than (10),
p2 values less than (20),
p3 values less than (30),
p4 values less than (40),
p5 values less than (50),
pn values less than maxvalue);
The PARTITION
keyword is not optional as part of the partition definition. You must use this syntax:
CREATE OR REPLACE TABLE t1 (x INT)
PARTITION BY RANGE(x) (
partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than (30),
partition p4 values less than (40),
partition p5 values less than (50),
partition pn values less than maxvalue);
CREATE TABLE
can also be used to create a SEQUENCE. See CREATE SEQUENCE and Sequence Overview.
MariaDB supports Atomic DDL. CREATE TABLE
is atomic, except for CREATE OR REPLACE
, which are only crash-safe.
-
CREATE TABLE IF NOT EXISTS test (
a BIGINT auto_increment PRIMARY KEY,
name VARCHAR(128) charset utf8,
KEY name (name(32))
) engine=InnoDB default charset latin1;
This example shows a couple of things:
Usage of IF NOT EXISTS
; If the table already existed, it will not be created. There will not be any error for the client, just a warning.
How to create a PRIMARY KEY
that is automatically generated.
How to specify a table-specific character set and another for a column.
How to create an index (name
) that is only partly indexed (to save space).
The following clauses will work:
CREATE TABLE t1(
a int DEFAULT (1+1),
b int DEFAULT (a+1),
expires DATETIME DEFAULT(NOW() + INTERVAL 1 YEAR),
x BLOB DEFAULT USER()
);
Storage engines can add their own attributes for columns, indexes and tables
Variable slave-ddl-exec-mode
This page is licensed: GPLv2, originally from fill_help_tables.sql
This page is licensed: GPLv2, originally from fill_help_tables.sql
Generated columns can be virtual or persistent (stored).
<type> [GENERATED ALWAYS] AS ( <expression> )
[VIRTUAL | PERSISTENT | STORED] [UNIQUE] [UNIQUE KEY] [COMMENT <text>]
MariaDB's generated columns syntax is designed to be similar to the syntax for Microsoft SQL Server's computed columns and Oracle Database's virtual columns. The syntax is also compatible with the syntax for MySQL's generated columns.
MariaDB's generated columns syntax is designed to be similar to the syntax for Microsoft SQL Server's computed columns and Oracle Database's virtual columns. The syntax is not compatible with the syntax for MySQL's generated columns.
A generated column is a column in a table that cannot explicitly be set to a specific value in a DML query. Instead, its value is automatically generated based on an expression. This expression might generate the value based on the values of other columns in the table, or it might generate the value by calling built-in functions or user-defined functions (UDFs).
There are two types of generated columns:
PERSISTENT
(a.k.a. STORED
): This type's value is actually stored in the table.
VIRTUAL
: This type's value is not stored at all. Instead, the value is generated dynamically when the table is queried. This type is the default.
Generated columns are also sometimes called computed columns or virtual columns.
Generated columns can only be used with storage engines which support them. If you try to use a storage engine that does not support them, then you will see an error similar to the following:
ERROR 1910 (HY000): TokuDB storage engine does not support computed columns
A column in a MERGE table can be built on a PERSISTENT
generated column.
However, a column in a MERGE table can not be defined as a VIRTUAL
and PERSISTENT
generated column.
All data types are supported when defining generated columns.
Using the ZEROFILL column option is supported when defining generated columns.
Using the AUTO_INCREMENT column option is not supported when defining generated columns.
Using the AUTO_INCREMENT column option is supported when defining generated columns.
It does not work correctly, though. See MDEV-11117.
Using a generated column as a table's primary key is not supported. See MDEV-5590 for more information. If you try to use one as a primary key, then you will see an error similar to the following:
ERROR 1903 (HY000): Primary key cannot be defined upon a computed column
Using PERSISTENT
generated columns as part of a foreign key is supported.
Referencing PERSISTENT
generated columns as part of a foreign key is also supported.
However, using the ON UPDATE CASCADE
, ON UPDATE SET NULL
, or ON DELETE SET NULL
clauses is not supported. If you try to use an unsupported clause, then you will see an error similar to the following:
ERROR 1905 (HY000): Cannot define foreign key with ON UPDATE SET NULL clause on a computed column
Defining indexes on both VIRTUAL
and PERSISTENT
generated columns is supported.
If an index is defined on a generated column, then the optimizer considers using it in the same way as indexes based on "real" columns.
The optimizer can recognize use of indexed virtual column expressions in the WHERE
clause and use them to construct range and ref(const) accesses. See Virtual Column Support in the Optimizer.
The optimizer cannot recognize use of indexed virtual column expressions in the WHERE
clause and use them to construct range and ref(const) accesses. See Virtual Column Support in the Optimizer.
Generated columns are used in DML queries just as if they were "real" columns.
However, VIRTUAL
and PERSISTENT
generated columns differ in how their data is stored.
Values for PERSISTENT
generated columns are generated whenever a DML queries inserts or updates the row with the special DEFAULT
value. This generates the columns value, and it is stored in the table like the other "real" columns. This value can be read by other DML queries just like the other "real" columns.
Values for VIRTUAL
generated columns are not stored in the table. Instead, the value is generated dynamically whenever the column is queried. If other columns in a row are queried, but the VIRTUAL
generated column is not one of the queried columns, then the column's value is not generated.
The SELECT statement supports generated columns.
Generated columns can be referenced in the INSERT, UPDATE, and DELETE statements.
However, VIRTUAL
or PERSISTENT
generated columns cannot be explicitly set to any other values than NULL
or DEFAULT. If a generated column is explicitly set to any other value, then the outcome depends on whether strict mode is enabled in sql_mode. If it is not enabled, then a warning will be raised and the default generated value will be used instead. If it is enabled, then an error will be raised instead.
The CREATE TABLE statement has limited support for generated columns.
It supports defining generated columns in a new table.
It supports using generated columns to partition tables.
It does not support using the versioning clauses with generated columns.
The ALTER TABLE statement has limited support for generated columns.
It supports the MODIFY
and CHANGE
clauses for PERSISTENT
generated columns.
It does not support the MODIFY
clause for VIRTUAL
generated columns if ALGORITHM is not set to COPY
. See MDEV-15476 for more information.
It does not support the CHANGE
clause for VIRTUAL
generated columns if ALGORITHM is not set to COPY
. See MDEV-17035 for more information.
It does not support altering a table if ALGORITHM is not set to COPY
if the table has a VIRTUAL
generated column that is indexed. See MDEV-14046 for more information.
It does not support adding a VIRTUAL
generated column with the ADD
clause if the same statement is also adding other columns if ALGORITHM is not set to COPY
. See MDEV-17468 for more information.
It also does not support altering an existing column into a VIRTUAL
generated column.
It supports using generated columns to partition tables.
It does not support using the versioning clauses with generated columns.
The SHOW CREATE TABLE statement supports generated columns.
The DESCRIBE statement can be used to check whether a table has generated columns.
You can tell which columns are generated by looking for the ones where the Extra
column is set to either VIRTUAL
or PERSISTENT
. For example:
DESCRIBE table1;
+-------+-------------+------+-----+---------+------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+------------+
| a | int(11) | NO | | NULL | |
| b | varchar(32) | YES | | NULL | |
| c | int(11) | YES | | NULL | VIRTUAL |
| d | varchar(5) | YES | | NULL | PERSISTENT |
+-------+-------------+------+-----+---------+------------+
Generated columns can be properly referenced in the NEW
and OLD
rows in triggers.
Stored procedures support generated columns.
The HANDLER statement supports generated columns.
Most legal, deterministic expressions which can be calculated are supported in expressions for generated columns.
Most built-in functions are supported in expressions for generated columns.
However, some built-in functions can't be supported for technical reasons. For example, If you try to use an unsupported function in an expression, an error is generated similar to the following:
ERROR 1901 (HY000): Function or expression 'dayname()' cannot be used in the GENERATED ALWAYS AS clause of `v`
Subqueries are not supported in expressions for generated columns because the underlying data can change.
Using anything that depends on data outside the row is not supported in expressions for generated columns.
Stored functions are not supported in expressions for generated columns. See MDEV-17587 for more information.
Non-deterministic built-in functions are supported in expressions for not indexed VIRTUAL
generated columns.
Non-deterministic built-in functions are not supported in expressions for PERSISTENT
or indexed VIRTUAL
generated columns.
User-defined functions (UDFs) are supported in expressions for generated columns.
However, MariaDB can't check whether a UDF is deterministic, so it is up to the user to be sure that they do not use non-deterministic UDFs with VIRTUAL
generated columns.
Defining a generated column based on other generated columns defined before it in the table definition is supported. For example:
CREATE TABLE t1 (a int as (1), b int as (a));
However, defining a generated column based on other generated columns defined after in the table definition is not supported in expressions for generation columns because generated columns are calculated in the order they are defined.
Using an expression that exceeds 255 characters in length is supported in expressions for generated columns. The new limit for the entire table definition, including all expressions for generated columns, is 65,535 bytes.
Using constant expressions is supported in expressions for generated columns. For example:
When a generated column is PERSISTENT
or indexed, the value of the expression needs to be consistent regardless of the SQL Mode flags in the current session. If it is not, then the table will be seen as corrupted when the value that should actually be returned by the computed expression and the value that was previously stored and/or indexed using a different sql_mode setting disagree.
There are currently two affected classes of inconsistencies: character padding and unsigned subtraction:
For a VARCHAR
or TEXT
generated column the length of the value returned can vary depending on the PAD_CHAR_TO_FULL_LENGTH sql_mode flag. To make the value consistent, create the generated column using an RTRIM() or RPAD() function. Alternately, create the generated column as a CHAR
column so that its data is always fully padded.
If a SIGNED
generated column is based on the subtraction of an UNSIGNED
value, the resulting value can vary depending on how large the value is and the NO_UNSIGNED_SUBTRACTION sql_mode flag. To make the value consistent, use CAST() to ensure that each UNSIGNED
operand is SIGNED
before the subtraction.
A fatal error is generated when trying to create a generated column whose value can change depending on the SQL Mode when its data is PERSISTENT
or indexed. For an existing generated column that has a potentially inconsistent value, a warning about a bad expression is generated the first time it is used (if warnings are enabled).
For an existing generated column that has a potentially inconsistent value, a warning about a bad expression is generated the first time it is used (if warnings are enabled).
A potentially inconsistent generated column outputs a warning when created or first used (without restricting the creation).
A potentially inconsistent generated column does not output a warning when created or first used.
Here is an example of two tables that are warned about:
CREATE TABLE bad_pad (
txt CHAR(5),
-- CHAR -> VARCHAR or CHAR -> TEXT can't be persistent or indexed:
vtxt VARCHAR(5) AS (txt) PERSISTENT
);
CREATE TABLE bad_sub (
num1 BIGINT UNSIGNED,
num2 BIGINT UNSIGNED,
-- The resulting value can vary for some large values
vnum BIGINT AS (num1 - num2) VIRTUAL,
KEY(vnum)
);
The warnings look like this:
Warning (Code 1901): Function or expression '`txt`' cannot be used in the GENERATED ALWAYS AS clause of `vtxt`
Warning (Code 1105): Expression depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
Warning (Code 1901): Function or expression '`num1` - `num2`' cannot be used in the GENERATED ALWAYS AS clause of `vnum`
Warning (Code 1105): Expression depends on the @@sql_mode value NO_UNSIGNED_SUBTRACTION
To work around the issue, force the padding or type to make the generated column's expression return a consistent value. For example:
CREATE TABLE good_pad (
txt CHAR(5),
-- Using RTRIM() or RPAD() makes the value consistent:
vtxt VARCHAR(5) AS (RTRIM(txt)) PERSISTENT,
-- When not persistent or indexed, it is OK for the value to vary by mode:
vtxt2 VARCHAR(5) AS (txt) VIRTUAL,
-- CHAR -> CHAR is always OK:
txt2 CHAR(5) AS (txt) PERSISTENT
);
CREATE TABLE good_sub (
num1 BIGINT UNSIGNED,
num2 BIGINT UNSIGNED,
-- The indexed value will always be consistent in this expression:
vnum BIGINT AS (CAST(num1 AS SIGNED) - CAST(num2 AS SIGNED)) VIRTUAL,
KEY(vnum)
);
Here is an example of two tables whose creation is rejected:
CREATE TABLE bad_pad (
txt CHAR(5),
-- CHAR -> VARCHAR or CHAR -> TEXT can't be persistent or indexed:
vtxt VARCHAR(5) AS (txt) PERSISTENT
);
CREATE TABLE bad_sub (
num1 BIGINT UNSIGNED,
num2 BIGINT UNSIGNED,
-- The resulting value can vary for some large values
vnum BIGINT AS (num1 - num2) VIRTUAL,
KEY(vnum)
);
The STORED
keyword is supported as an alias for the PERSISTENT
keyword.
Tables created with MySQL 5.7 or later that contain MySQL's generated columns can be imported into MariaDB without a dump and restore.
Generated columns are subject to various constraints in other DBMSs that are not present in MariaDB's implementation. Generated columns may also be called computed columns or virtual columns in different implementations. The various details for a specific implementation can be found in the documentation for each specific DBMS.
MariaDB's generated columns implementation does not enforce the following restrictions that are present in Microsoft SQL Server's computed columns implementation:
MariaDB allows server variables in generated column expressions, including those that change dynamically, such as warning_count.
MariaDB allows the CONVERT_TZ() function to be called with a named time zone as an argument, even though time zone names and time offsets are configurable.
MariaDB allows the CAST() function to be used with non-unicode character sets, even though character sets are configurable and differ between binaries/versions.
MariaDB allows FLOAT expressions to be used in generated columns. Microsoft SQL Server considers these expressions to be "imprecise" due to potential cross-platform differences in floating-point implementations and precision.
Microsoft SQL Server requires the ARITHABORT mode to be set, so that division by zero returns an error, and not a NULL.
Microsoft SQL Server enforces the above restrictions by doing one of the following things:
Refusing to create computed columns.
Refusing to allow updates to a table containing them.
Refusing to use an index over such a column if it can not be guaranteed that the expression is fully deterministic.
In MariaDB, as long as the sql_mode, language, and other settings that were in effect during the CREATE TABLE remain unchanged, the generated column expression will always be evaluated the same. If any of these things change, then please be aware that the generated column expression might not be evaluated the same way as it previously was.
If you try to update a virtual column, you will get an error if the default strict mode is enabled in sql_mode, or a warning otherwise.
Generated columns was originally developed by Andrey Zhakov. It was then modified by Sanja Byelkin and Igor Babaev at Monty Program for inclusion in MariaDB. Monty did the work on MariaDB 10.2 to lift some of the limitations.
Here is an example table that uses both VIRTUAL
andPERSISTENT
virtual columns:
USE TEST;
CREATE TABLE table1 (
a INT NOT NULL,
b VARCHAR(32),
c INT AS (a MOD 10) VIRTUAL,
d VARCHAR(5) AS (LEFT(b,5)) PERSISTENT);
If you describe the table, you can easily see which columns are virtual by looking in the "Extra" column:
DESCRIBE table1;
+-------+-------------+------+-----+---------+------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+------------+
| a | int(11) | NO | | NULL | |
| b | varchar(32) | YES | | NULL | |
| c | int(11) | YES | | NULL | VIRTUAL |
| d | varchar(5) | YES | | NULL | PERSISTENT |
+-------+-------------+------+-----+---------+------------+
To find out what function(s) generate the value of the virtual column you can use SHOW CREATE TABLE
:
SHOW CREATE TABLE table1;
| table1 | CREATE TABLE `table1` (
`a` int(11) NOT NULL,
`b` varchar(32) DEFAULT NULL,
`c` int(11) AS (a mod 10) VIRTUAL,
`d` varchar(5) AS (left(b,5)) PERSISTENT
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
If you try to insert non-default values into a virtual column, you will receive a warning and what you tried to insert will be ignored and the derived value inserted instead:
WARNINGS;
Show warnings enabled.
INSERT INTO table1 VALUES (1, 'some text',default,default);
Query OK, 1 row affected (0.00 sec)
INSERT INTO table1 VALUES (2, 'more text',5,default);
Query OK, 1 row affected, 1 warning (0.00 sec)
Warning (Code 1645): The value specified for computed column 'c' in table 'table1' has been ignored.
INSERT INTO table1 VALUES (123, 'even more text',default,'something');
Query OK, 1 row affected, 2 warnings (0.00 sec)
Warning (Code 1645): The value specified for computed column 'd' in table 'table1' has been ignored.
Warning (Code 1265): Data truncated for column 'd' at row 1
SELECT * FROM table1;
+-----+----------------+------+-------+
| a | b | c | d |
+-----+----------------+------+-------+
| 1 | some text | 1 | some |
| 2 | more text | 2 | more |
| 123 | even more text | 3 | even |
+-----+----------------+------+-------+
3 rows in set (0.00 sec)
If the ZEROFILL
clause is specified, it should be placed directly after the type definition, before the AS (<expression>)
:
CREATE TABLE table2 (a INT, b INT ZEROFILL AS (a*2) VIRTUAL);
INSERT INTO table2 (a) VALUES (1);
SELECT * FROM table2;
+------+------------+
| a | b |
+------+------------+
| 1 | 0000000002 |
+------+------------+
1 row in set (0.00 sec)
You can also use virtual columns to implement a "poor man's partial index". See example at the end of Unique Index.
Putting Virtual Columns to good use on the mariadb.com blog.
This page is licensed: CC BY-SA / Gnu FDL
Invisible columns (sometimes also called hidden columns) are hidden in certain contexts.
Columns can be given an INVISIBLE
attribute in a CREATE TABLE or ALTER TABLE statement. These columns will then not be listed in the results of a SELECT * statement, nor do they need to be assigned a value in an INSERT statement, unless INSERT explicitly mentions them by name.
Since SELECT *
does not return the invisible columns, new tables or views created in this manner will have no trace of the invisible columns. If specifically referenced in the SELECT statement, the columns will be brought into the view/new table, but the INVISIBLE attribute will not.
Invisible columns can be declared as NOT NULL
, but then require a DEFAULT
value.
CREATE TABLE t (x INT INVISIBLE);
ERROR 1113 (42000): A table must have at least 1 column
CREATE TABLE t (x INT, y INT INVISIBLE, z INT INVISIBLE NOT NULL);
ERROR 4106 (HY000): Invisible column `z` must have a default value
CREATE TABLE t (x INT, y INT INVISIBLE, z INT INVISIBLE NOT NULL DEFAULT 4);
INSERT INTO t VALUES (1),(2);
INSERT INTO t (x,y) VALUES (3,33);
SELECT * FROM t;
+------+
| x |
+------+
| 1 |
| 2 |
| 3 |
+------+
SELECT x,y,z FROM t;
+------+------+---+
| x | y | z |
+------+------+---+
| 1 | NULL | 4 |
| 2 | NULL | 4 |
| 3 | 33 | 4 |
+------+------+---+
DESC t;
+-------+---------+------+-----+---------+-----------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-----------+
| x | int(11) | YES | | NULL | |
| y | int(11) | YES | | NULL | INVISIBLE |
| z | int(11) | NO | | 4 | INVISIBLE |
+-------+---------+------+-----+---------+-----------+
ALTER TABLE t MODIFY x INT INVISIBLE, MODIFY y INT, MODIFY z INT NOT NULL DEFAULT 4;
DESC t;
+-------+---------+------+-----+---------+-----------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-----------+
| x | int(11) | YES | | NULL | INVISIBLE |
| y | int(11) | YES | | NULL | |
| z | int(11) | NO | | 4 | |
+-------+---------+------+-----+---------+-----------+
Creating a view from a table with hidden columns:
CREATE VIEW v1 AS SELECT * FROM t;
DESC v1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| y | int(11) | YES | | NULL | |
| z | int(11) | NO | | 4 | |
+-------+---------+------+-----+---------+-------+
CREATE VIEW v2 AS SELECT x,y,z FROM t;
DESC v2;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| x | int(11) | YES | | NULL | |
| y | int(11) | YES | | NULL | |
| z | int(11) | NO | | 4 | |
+-------+---------+------+-----+---------+-------+
Adding a Surrogate Primary Key:
CREATE TABLE t1 (x BIGINT UNSIGNED NOT NULL, y VARCHAR(16), z TEXT);
INSERT INTO t1 VALUES (123, 'qq11', 'ipsum');
INSERT INTO t1 VALUES (123, 'qq22', 'lorem');
ALTER TABLE t1 ADD pkid SERIAL PRIMARY KEY invisible FIRST;
INSERT INTO t1 VALUES (123, 'qq33', 'amet');
SELECT * FROM t1;
+-----+------+-------+
| x | y | z |
+-----+------+-------+
| 123 | qq11 | ipsum |
| 123 | qq22 | lorem |
| 123 | qq33 | amet |
+-----+------+-------+
SELECT pkid, z FROM t1;
+------+-------+
| pkid | z |
+------+-------+
| 1 | ipsum |
| 2 | lorem |
| 3 | amet |
+------+-------+
This page is licensed: CC BY-SA / Gnu FDL
When a CREATE TABLE or ALTER TABLE command is issued, MariaDB silently changes a column specification in the following cases:
PRIMARY KEY columns are always NOT NULL.
TIMESTAMP columns are always NOT NULL, and display sizes are discarded.
A row-size limit of 65535 bytes applies.
If strict SQL mode is not enabled (by default, it is), a VARCHAR column longer than 65535 become TEXT, and a VARBINARY columns longer than 65535 becomes a BLOB. If strict mode is enabled the silent changes will not be made, and an error will occur.
If a USING clause specifies an index that's not permitted by the storage engine, the engine will instead use another available index type that can be applied without affecting results.
If the CHARACTER SET binary attribute is specified, the column is created as the matching binary data type. A TEXT becomes a BLOB, CHAR a BINARY and VARCHAR a VARBINARY. ENUMs and SETs are created as defined.
To ease imports from other RDBMSs, MariaDB also silently maps the following data types:
Currently, all MySQL types are supported in MariaDB.
For type mapping between Cassandra and MariaDB, see Cassandra storage engine.
Silent changes in action:
CREATE TABLE SilenceIsGolden
(
f1 TEXT CHARACTER SET BINARY,
f2 VARCHAR(15) CHARACTER SET BINARY,
f3 CHAR CHARACTER SET BINARY,
f4 ENUM('x','y','z') CHARACTER SET BINARY,
f5 VARCHAR (65536),
f6 VARBINARY (65536),
f7 INT1
);
Query OK, 0 rows affected, 2 warnings (0.31 sec)
SHOW WARNINGS;
+-------+------+-----------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------+
| Note | 1246 | Converting column 'f5' from VARCHAR to TEXT |
| Note | 1246 | Converting column 'f6' from VARBINARY to BLOB |
+-------+------+-----------------------------------------------+
DESCRIBE SilenceIsGolden;
+-------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------------+------+-----+---------+-------+
| f1 | blob | YES | | NULL | |
| f2 | varbinary(15) | YES | | NULL | |
| f3 | binary(1) | YES | | NULL | |
| f4 | enum('x','y','z') | YES | | NULL | |
| f5 | mediumtext | YES | | NULL | |
| f6 | mediumblob | YES | | NULL | |
| f7 | tinyint(4) | YES | | NULL | |
+-------+-------------------+------+-----+---------+-------+
This page is licensed: CC BY-SA / Gnu FDL