Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Explore the CREATE statements used to define new database objects. This guide covers syntax for creating databases, tables, indexes, views, and stored routines.
Create a new database container. This command initializes a new database namespace with optional character set and collation settings.
CREATE DATABASE creates a database with the given name. To use this statement, you need the for the database. CREATE SCHEMA is a synonym for CREATE DATABASE.
For valid identifiers to use as database names, see .
If the optional OR REPLACE clause is used, it acts as a shortcut for:
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 .
Comments added for databases do not exist.
Setting the . See for more details.
This page is licensed: GPLv2, originally from
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 a log file group for NDB Cluster. This command allocates undo log space on disk for NDB Disk Data tables.
This page is licensed: CC BY-SA / Gnu FDL
DROP DATABASE IF EXISTS db_name;
CREATE DATABASE db_name ...;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 |
+-------+------+----------------------------------------------+CREATE DATABASE czech_slovak_names
CHARACTER SET = 'keybcs2'
COLLATE = 'keybcs2_bin';CREATE DATABASE presentations COMMENT 'Presentations for conferences';Schedule a new event for automatic execution. This statement defines a scheduled task that runs SQL commands at specific times or intervals.
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:
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 .
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 .
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 , 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 .
The following example shows how to create an event that will be triggered tomorrow at a certain time:
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 .
In the following example, next month a recurring event will be triggered hourly for a week:
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 ENABLEd 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 . 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 (the column is named EVENT_COMMENT).
Minimal CREATE EVENT statement:
An event that will be triggered tomorrow at a certain time:
Next month a recurring event will be triggered hourly for a week:
OR REPLACE and IF NOT EXISTS:
This page is licensed: GPLv2, originally from
Define the interface for a stored package. This Oracle-compatible statement declares the public variables and subroutines of a package.
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}Create a tablespace for data storage. This command defines a physical file container for storing table data, primarily for InnoDB or NDB engines.
This page is licensed: GPLv2, originally from fill_help_tables.sql
CREATE EVENT myevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
UPDATE myschema.mytable SET mycol = mycol + 1;CREATE EVENT example
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY + INTERVAL 3 HOUR
DO something;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;CREATE EVENT myevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
UPDATE myschema.mytable SET mycol = mycol + 1;CREATE EVENT example
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY + INTERVAL 3 HOUR
DO something;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;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 |
+-------+------+--------------------------------+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
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.
This page is licensed: CC BY-SA / Gnu FDL
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 }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 ;Implement the logic for a stored package. This statement defines the private variables and code for the subroutines declared in the package specification.
The CREATE PACKAGE BODY statement can be used in any mode.
The CREATE PACKAGE BODY statement can be used in .
In Oracle mode, the PL/SQL dialect is used, while in non-Oracle mode, SQL/PSM is used.
The CREATE PACKAGE BODY statement creates the package body for a stored package. The package specification must be previously created using the 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
This page is licensed: CC BY-SA / Gnu FDL
Explains how to define columns that are hidden from wildcard SELECT queries but remain accessible when explicitly referenced, facilitating schema changes without breaking existing applications.
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.
Creating a view from a table with hidden columns:
Adding a Surrogate Primary Key:
This page is licensed: CC BY-SA / Gnu FDL
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 | |
+-------+---------+------+-----+---------+-----------+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 | |
+-------+---------+------+-----+---------+-------+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 |
+------+-------+Add an index to an existing table. This statement improves query performance by creating a data structure for rapid lookups on specific columns.
The CREATE INDEX statement is used to add indexes to a table. Indexes can be created at the same as the table, with the 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 . See . CREATE INDEX cannot be used to create a ; use ALTER TABLE instead.
If another connection is using the table, a is active, and this statement will wait until the lock is released. This is also true for non-transactional tables.
Another shortcut, , allows the removal of an index.
For valid identifiers to use as index names, see .
For limits on InnoDB indexes, see .
Note that KEY_BLOCK_SIZE is currently ignored in CREATE INDEX, although it is included in the output of .
Executing the CREATE INDEX statement requires the privilege for the table or the database.
Online DDL is supported with the and clauses.
See 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 for information about index definitions.
Set the lock wait timeout. See .
See for more information.
See 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 client, then the progress report might look like this::
The progress report is also shown in the output of the statement and in the contents of the table.
See for more information.
The WITHOUT OVERLAPS clause allows you to constrain a primary or unique index such that cannot overlap. It can be used like this:
WITHOUT OVERLAPS is not available.
Creating a unique index:
OR REPLACE and IF NOT EXISTS:
This page is licensed: GPLv2, originally from
Define a connection to a remote server. This command registers server details for use with the FEDERATED or SPIDER storage engines.
This statement creates the definition of a server for use with the , , , or storage engine. The CREATE SERVER statement creates a new row in the table within the mysql database. This statement requires the privilege.
This statement creates the definition of a server for use with the , , , or storage engine. The CREATE SERVER statement creates a new row in the table within the mysql database. This statement requires the 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 )
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 the table that can later be used with the CREATE TABLE statement when creating a , , or 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.
removes a previously created server definition.
CREATE SERVER is not written to the , irrespective of the being used and therefore will not replicate.
replicates the CREATE SERVER, and statements.
does not replicate the CREATE SERVER, and statements.
For valid identifiers to use as server names, see .
The statement can be used to show the CREATE SERVER statement that created a given server definition.
The 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:
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.
OR REPLACE and IF NOT EXISTS:
This page is licensed: GPLv2, originally from
Explore Silent Column Changes in MariaDB. Learn when the server automatically modifies column definitions or data types during table creation to ensure engine compatibility.
When a or command is issued, MariaDB silently changes a column specification in the following cases:
columns are always NOT NULL.
Any trailing spaces from and values are discarded.
columns are always NOT NULL, and display sizes are discarded.
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_codeSET 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 ;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}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}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 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.
The RETURN clause can return a function body. In newer versions of MariaDB, it can also return a cursor (using SYS_REFCURSOR).
RETURN func_body
The RETURN clause can return a function body (a valid SQL PROCEDURE statement).
RETURN SYS_REFCURSOR
It can also return a cursor, if the function was declared with a SYS_REFCURSOR variable, like this:
Alternatively, a cursor can be returned in an OUT parameter, see this section.
RETURN func_body
The RETURN clause can return a function body (a valid SQL PROCEDURE statement).
It is possible to create stored aggregate functions as well. See Stored Aggregate Functions for details.
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.
Quantifiers are not available.
A cursor can be returned, like this:
This feature is not available.
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 which 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:
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 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 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.
To allow the user the get the maximum salary for a department, define a function and grant the EXECUTE privilege:
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.
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.
Character set and collation:
This page is licensed: GPLv2, originally from fill_help_tables.sql
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:
BOOL
BOOLEAN
CHARACTER VARYING(M)
(M)
FIXED
FLOAT4
FLOAT8
Currently, all MySQL types are supported in MariaDB.
For type mapping between Cassandra and MariaDB, see Cassandra storage engine.
Silent changes in action:
This page is licensed: CC BY-SA / Gnu FDL
CREATE INDEX i ON tab (num);
Stage: 1 of 2 'copy to tmp table' 46% of stageCREATE UNIQUE INDEX u ON rooms (room_number, p WITHOUT OVERLAPS);CREATE UNIQUE INDEX HomePhone ON Employees(Home_Phone);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' |
+-------+------+-------------------------+DROP SERVER IF EXISTS name;
CREATE SERVER server_name ...;CREATE SERVER s
FOREIGN DATA WRAPPER mariadb
OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');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 |
+-------+------+----------------------------------------------------------------+CREATE FUNCTION f1 RETURN SYS_REFCURSOR AS
c SYS_REFCURSOR;
BEGIN
OPEN c FOR SELECT a FROM t1 ORDER BY a;
RETURN c;
END;
/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 |
+------------------+CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10),(20);
CREATE FUNCTION f1 RETURN SYS_REFCURSOR AS
c SYS_REFCURSOR;
BEGIN
OPEN c FOR SELECT a FROM t1 ORDER BY a;
RETURN c;
END;
/
CREATE PROCEDURE p1 AS
a INT;
c SYS_REFCURSOR DEFAULT f1();
BEGIN
LOOP
FETCH c INTO a;
EXIT WHEN c%NOTFOUND;
dbms_output.put_line(a);
END LOOP;
CLOSE c;
END;
/
CALL p1();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
[RETURN SYS_REFCURSOR]
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 statementDROP FUNCTION IF EXISTS function_name;
CREATE FUNCTION function_name ...;CREATE TABLE employees (name TINYTEXT, dept TINYTEXT, salary INT);
GRANT SELECT (name, dept) ON employees TO roger;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;CREATE FUNCTION hello (s CHAR(20))
RETURNS CHAR(50) DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!');
SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world! |
+----------------+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 ;CREATE FUNCTION hello2 (s CHAR(20))
RETURNS CHAR(50) CHARACTER SET 'utf8' COLLATE 'utf8_bin' DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!');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 | |
+-------+-------------------+------+-----+---------+-------+INT1
INT2
INT3
INT4
INT8
LONG VARBINARY
LONG VARCHAR
LONG
MIDDLEINT
NUMERIC
Generated columns can be virtual or persistent (stored).
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 . 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 or .
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:
, , and support generated columns.
A column in a 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 column option is supported when defining generated columns.
Using the column option is not supported when defining generated columns.
Using the column option is supported when defining generated columns.
It does not work correctly, though. See .
Using a generated column as a table's primary key is not supported. See for more information. If you try to use one as a primary key, then you will see an error similar to the following:
Using PERSISTENT generated columns as part of a is supported.
Referencing PERSISTENT generated columns as part of a 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:
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 .
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 .
Generated columns are used in 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 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 just like the other "real" columns.
The statement supports generated columns.
Generated columns can be referenced in the , , and statements.
However, VIRTUAL or PERSISTENT generated columns cannot be explicitly set to any other values than NULL or . If a generated column is explicitly set to any other value, then the outcome depends on whether is enabled in . 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 statement has limited support for generated columns.
It supports defining generated columns in a new table.
It supports using generated columns to .
It does not support using the with generated columns.
The 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 is not set to COPY. See for more information.
The statement supports generated columns.
The 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:
Generated columns can be properly referenced in the NEW and OLD rows in .
support generated columns.
The statement supports generated columns.
Most legal, deterministic expressions which can be calculated are supported in expressions for generated columns.
Most are supported in expressions for generated columns.
However, some 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:
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.
are not supported in expressions for generated columns. See for more information.
Non-deterministic are supported in expressions for not indexed VIRTUAL generated columns.
Non-deterministic are not supported in expressions for PERSISTENT or indexed VIRTUAL generated columns.
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:
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 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 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 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 flag. To make the value consistent, use to ensure that each UNSIGNED
A fatal error is generated when trying to create a generated column whose value can change depending on the 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:
The warnings look like this:
To work around the issue, force the padding or type to make the generated column's expression return a consistent value. For example:
Here is an example of two tables whose creation is rejected:
The STORED keyword is supported as an alias for the PERSISTENT keyword.
Tables created with MySQL 5.7 or later that contain 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 implementation:
MariaDB allows in generated column expressions, including those that change dynamically, such as .
MariaDB allows the function to be called with a named as an argument, even though time zone names and time offsets are configurable.
MariaDB allows the function to be used with non-unicode , even though character sets are configurable and differ between binaries/versions.
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 , 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 is enabled in , 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 to lift some of the limitations.
Here is an example table that uses both VIRTUAL andPERSISTENT virtual columns:
If you describe the table, you can easily see which columns are virtual by looking in the "Extra" column:
To find out what function(s) generate the value of the virtual column you can use SHOW CREATE TABLE:
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:
If the ZEROFILL clause is specified, it should be placed directly after the type definition, before the AS (<expression>):
You can also use virtual columns to implement a "poor man's partial index". See example at the end of .
on the mariadb.com blog.
This page is licensed: CC BY-SA / Gnu FDL
<type> [GENERATED ALWAYS] AS ( <expression> )
[VIRTUAL | PERSISTENT | STORED] [UNIQUE] [UNIQUE KEY] [COMMENT <text>]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.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.
SIGNEDMicrosoft SQL Server requires the ARITHABORT mode to be set, so that division by zero returns an error, and not a NULL.
ERROR 1910 (HY000): TokuDB storage engine does not support computed columnsERROR 1903 (HY000): Primary key cannot be defined upon a computed columnERROR 1905 (HY000): Cannot define foreign key with ON UPDATE SET NULL clause on a computed columnDESCRIBE 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 |
+-------+-------------+------+-----+---------+------------+ERROR 1901 (HY000): Function or expression 'dayname()' cannot be used in the GENERATED ALWAYS AS clause of `v`CREATE TABLE t1 (a INT AS (1), b INT AS (a));CREATE TABLE t1 (a int as (1));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)
);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_SUBTRACTIONCREATE 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)
);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)
);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);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 |
+-------+-------------+------+-----+---------+------------+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 |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)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)Define a new table structure. This fundamental command specifies columns, data types, indexes, and storage engine options for storing data.
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 , because it allows to create a table into a database, which contains data from other databases. See .
If a table with the same name exists, error 1050 results. Use to suppress this error and issue a note instead. Use to see notes.
The CREATE TABLE statement automatically commits the current transaction, except when using the keyword.
For valid identifiers to use as table names, see .
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 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 more robust if it has to rollback and repeat statements such as CREATE ... SELECT on replicas.
is basically the same as:
with the following exceptions:
If table_name was locked with it will continue to be locked after the statement.
Temporary tables are only dropped if the TEMPORARY keyword was used. (With , 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.
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 privilege on the database to create temporary tables. If no storage engine is specified, the setting will determine the engine.
temporary tables cannot be created by setting the 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.
temporary tables cannot be created by setting the 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.
By default, temporary tables are only created on the replica if the primary is using the .
The new deterministic rules for logging of temporary tables are:
The STATEMENT binlog format is used. If it is binlogged, 1 is stored in TABLE_SHARE->table_creation_was_logged. The user can change this behavior by setting to MIXED, STATEMENT in which case the create is logged in statement format also in MIXED mode (as before).
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 , only base tables. Attempting to use it on a view will result in an error:
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 renders it invalid. For example:
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:
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, CHECK constraints) 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 function can be used to force 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 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.
are not used during the execution of a CREATE ... SELECT.
If the table already exists, an error similar to the following will be returned:
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, can be used.
MariaDB accepts the shortcut format with a REFERENCES clause only in ALTER TABLE and CREATE TABLE statements, but that syntax does nothing. For example:
MariaDB will attempt to apply the constraint. See .
MariaDB accepts the shortcut format with a REFERENCES clause only in ALTER TABLE and CREATE TABLE statements, but that syntax does nothing. For example:
Each definition either creates a column in the table or specifies and index or constraint on one or more columns. See below for details on creating indexes.
Create a column by specifying a column name and a data type, optionally followed by column options. See 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 .
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 a row without specifying a value for that column, or if you specify for that column.
may also be used as the default value for a
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 in . You can even refer to earlier columns in the DEFAULT expression (excluding AUTO_INCREMENT columns):
The DEFAULT clause cannot contain any or , and a column used in the clause must already have been defined earlier in the statement.
It is possible to assign or columns a DEFAULT value.
You can also use DEFAULT ().
Use 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 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 to get the value most recently used by an statement.
If the ZEROFILL column option is specified for a column using a 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 , , or . ZEROFILL is a non-standard MariaDB and MySQL extension.
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 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.
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.
See the 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 statement to see column comments.
REF_SYSTEM_ID can be used to specify Spatial Reference System IDs for spatial data type columns. For example:
A generated column is a column in a table that cannot explicitly be set to a specific value in a . 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 or .
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 .
Certain columns may be compressed. See .
Columns may be made invisible, and hidden in certain contexts. See .
Columns may be explicitly marked as included from system versioning. See for details.
Columns may be explicitly marked as excluded from system versioning. See for details.
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 .
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 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 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 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.
If either clause is omitted, the default behavior for the omitted clause is RESTRICT.
See for more information.
Use the FULLTEXT keyword to create full-text indexes.
See for more information.
Use the SPATIAL keyword to create geometric indexes.
See for more information.
The KEY_BLOCK_SIZE index option is similar to the table option.
With the 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 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 storage engine ignores the KEY_BLOCK_SIZE index option. However, the statement may still report it for the index.
For information about the KEY_BLOCK_SIZE index option, see the table option below.
Each storage engine supports some or all index types. See 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
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 (, , , ). Not only ascending, but also descending, indexes can be used to optimize and ().
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 , this was only for compatibility with other DBMSs, but had no meaning in MariaDB. From , 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 (, , , ). From , not only ascending, but also descending, indexes can now be used to optimize
The maximum number of parts in an index is 32.
The WITH PARSER index option only applies to 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 .
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 storage engine.
Indexes can be specified to be ignored by the optimizer. See .
Indexes can be specified to be ignored by the optimizer. See .
MariaDB supports , or .
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 .
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 .
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 for more information.
For each individual table you create (or alter), you can set some table options. The general syntax for setting options is:
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 .
If the IGNORE_BAD_TABLE_OPTIONS is enabled, wrong table options generate a warning; otherwise, they generate an error.
[STORAGE] ENGINE specifies a for the table. If this option is not used, the default storage engine is used instead. That is, the session option value if it is set, or the value specified for the --default-storage-engine , or the default storage engine, . If the specified storage engine is not installed and active, the default value will be used, unless the NO_ENGINE_SUBSTITUTION 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 .
AUTO_INCREMENT specifies the initial value for the 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 and storage engines that have the 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 , which is utf8mb4 by default). See for details on setting the .
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 will be very fast. This option is only supported for and .
[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 , which uses utf8mb4_bin by default). See for details on setting the
COMMENT is a comment for the table. The maximum length is 2048 characters. Also used to define table parameters when creating a table.
CONNECTION is used to specify a server name or a connection string for a , , .
DATA DIRECTORY and INDEX DIRECTORY are supported for MyISAM and Aria, and DATA DIRECTORY is also supported by InnoDB if the server system variable is enabled, but only in CREATE TABLE, not in . 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 tables (use the partition options instead), or if the server has been invoked with the . To avoid the overwriting of old files with the same name that could be present in the directories, you can use (an error will be issued if files already exist). These options are ignored if the NO_DIR_IN_CREATE 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 ) 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 table. See for more information.
Aria does not support the ENCRYPTED table option. See .
See for more information.
The ENCRYPTION_KEY_ID table option can be used to manually set the encryption key of an table. See for more information.
Aria does not support the ENCRYPTION_KEY_ID table option. See .
See for more information.
For the 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 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 INSERTs 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 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 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 tables, and determines whether indexes and data should use page checksums for extra safety.
PAGE_COMPRESSED is used to enable for tables.
PAGE_COMPRESSION_LEVEL is used to set the compression level for for tables. The table must also have the 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 , the supported row formats are:
FIXED
DYNAMIC
COMPRESSED
The COMPRESSED row format can only be set by the command line tool.
See for more information.
For , the supported row formats are:
PAGE
FIXED
DYNAMIC.
See for more information.
For , 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 system variable. If the 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 system variable is set to ON, then an error will be raised.
See for more information.
Other storage engines do not support the ROW_FORMAT table option.
If the table is a , 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 is run. If set to DEFAULT, or left out, the value set by the system variable applies. See .
STATS_PERSISTENT indicates whether the InnoDB statistics created by 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 system variable will apply. Persistent statistics stored on disk allow the statistics to survive server restarts, and provide better query plan stability. See .
STATS_SAMPLE_PAGES indicates how many pages are used to sample index statistics. If 0 or DEFAULT, the default value, the value is used. See .
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 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 .
If the PARTITION BY clause is used, the table will be . A partition method must be explicitly indicated for partitions and subpartitions. Partition methods are:
[LINEAR] 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:
[LINEAR] can be used for subpartitions, too.
[LINEAR] is similar to HASH, but the index has an even distribution of data. Also, the expression can only be a column or a list of columns. VALUES LESS THAN and VALUES IN clauses can not be used with KEY.
partitions the rows using on a range of values, using the VALUES LESS THAN operator. VALUES IN is not allowed with RANGE
Only and 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 PARTITIONs must equal count.
Also see .
The PARTITION keyword is optional as part of the partition definition. Instead of this:
The following can be used:
The PARTITION keyword is not optional as part of the partition definition. You must use this syntax:
CREATE TABLE can also be used to create a . See and .
MariaDB supports . CREATE TABLE is atomic, except for CREATE OR REPLACE, which are only crash-safe.
-
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 .
How to specify a table-specific and another for a column.
The following clauses will work:
This page is licensed: GPLv2, originally from
CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...) [table_options ]... [partition_options]
CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)] [table_options ]... [partition_options]
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)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.Changes to temporary tables are only binlogged if and only if the CREATE was logged. The logging happens under STATEMENT or MIXED. If binlog_format=ROW, temporary table changes are not binlogged. A temporary table that is changed under ROW is marked as 'not up to date in binlog' and no future row changes are logged. Any usage of this temporary table will force row logging of other tables in any future statements using the temporary table to be row logged.
DROP TEMPORARY is binlogged only if the CREATE was binlogged.
In some contexts, temporary tables on the primary and replica can become inconsistent. One example is if a temporary table is updated with the value of a non deterministic function like UUID(), in which the change is never sent to the replica.
In some other contexts, while using MIXED mode, all changes will be logged in ROW mode while the user has any active temporary tables, even if the temporary tables are not used in the query. This depends on in which format some previous independent commands were logged.
There are many other pitfalls with logging temporary table to the replica.
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.
ORDER BYRTREE is the default for SPATIAL indexes, but if the storage engine does not support it BTREE can be used.
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.
name) that is only partly indexed (to save space).Storage engines can add their own attributes for columns, indexes and tables
Variable slave-ddl-exec-mode
CREATE OR REPLACE TABLE table_name (a INT);DROP TABLE IF EXISTS TABLE_NAME;
CREATE TABLE TABLE_NAME (a INT);CREATE VIEW v (mycol) AS SELECT 'abc';
CREATE TABLE v2 LIKE v;
ERROR 1347 (HY000): 'test.v' is not of type 'BASE TABLE'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'CREATE TABLE test (a INT NOT NULL, b CHAR(10)) ENGINE=MyISAM
SELECT 5 AS b, c, d FROM another_table;ERROR 1050 (42S01): Table 't' already existscreate_definition:
{ col_name column_definition | index_definition | period_definition | CHECK (expr) }
column_definition:
data_type
[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]
[reference_definition]
| data_type [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)CREATE TABLE b(for_key INT REFERENCES a(not_key));CREATE TABLE b(for_key INT REFERENCES a(not_key));CREATE TABLE t1 (a INT DEFAULT (1+1), b INT DEFAULT (a+1));
CREATE TABLE t2 (a BIGINT PRIMARY KEY DEFAULT UUID_SHORT());### 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 */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 |
+----------+---------------+-------+-------+--------+------+
CREATE TABLE t1(g GEOMETRY(9,4) REF_SYSTEM_ID=101);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 ACTIONperiod_definition:
PERIOD FOR [time_period_name | SYSTEM_TIME] (start_column_name, end_column_name)CREATE TABLE t1 (a INT CHECK(a>0) ,b INT CHECK (b> 0), CONSTRAINT abc CHECK (a>b));<OPTION_NAME> = <option_value>, [<OPTION_NAME> = <option_value> ...]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 VERSIONINGpartition_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]CREATE TABLE t1 (a INT, b CHAR(5), c DATETIME)
PARTITION BY HASH ( YEAR(c) );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 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);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 IF NOT EXISTS test (
a BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(128) CHARSET utf8,
KEY name (name(32))
) ENGINE=InnoDB DEFAULT CHARSET latin1;CREATE TABLE t1(
a INT DEFAULT (1+1),
b INT DEFAULT (a+1),
expires DATETIME DEFAULT(NOW() + INTERVAL 1 YEAR),
x BLOB DEFAULT USER()
);