CREATE TABLE

CREATE TABLE

To create a table in the currently selected database:

CREATE TABLE customers (
   customer_id BIGINT AUTO_INCREMENT NOT NULL,
   customer_name VARCHAR(500) NOT NULL,
   customer_email VARCHAR(200),
   PRIMARY KEY(customer_id)
);

The resulting customers table will have 3 data columns and 1 primary key.

The built-in function DATABASE() returns the currently selected database.

To explicitly specify a database for the new table, use the DATABASE_NAME.TABLE_NAME notation:

CREATE TABLE hq_sales.customers (
   customer_id BIGINT AUTO_INCREMENT NOT NULL,
   customer_name VARCHAR(500) NOT NULL,
   customer_email VARCHAR(200),
   PRIMARY KEY(customer_id)
);

IF NOT EXISTS

By default, if you try to create a table with a name that already exists in the selected database, an error is raised.

To prevent an error when the specified table is already present, add the IF NOT EXISTS clause:

CREATE TABLE IF NOT EXISTS customers (
   customer_id BIGINT AUTO_INCREMENT NOT NULL,
   customer_name VARCHAR(500) NOT NULL,
   customer_email VARCHAR(200),
   PRIMARY KEY(customer_id)
);

If the specified table does not exist, it will be created. If the specified table already exists, the query does not raise an error.

Table Options

Table options are used in CREATE TABLE and ALTER TABLE statements to define table characteristics, for example, whether the table's representations are in-memory or on-disk, or the number of replicas MariaDB Xpand maintains for the table's slices.

The general syntax for setting table options is:

<OPTION_NAME> = <OPTION_VALUE>, [<OPTION_NAME> = <OPTION_VALUE> ...]

The equal sign (=) is optional.

For additional information on table options accepted by MariaDB Xpand and options supported just for compatibility purposes, see "Table Options with MariaDB Xpand".

SLICES Table Option

The SLICES table option sets the number of initial slices for the table. Slices are distributed throughout the cluster to facilitate evenly distributed query processing.

This statement slices the customers table:

CREATE TABLE customers (
   customer_id BIGINT AUTO_INCREMENT NOT NULL,
   customer_name VARCHAR(500) NOT NULL,
   customer_email VARCHAR(200),
   PRIMARY KEY(customer_id)
) SLICES = 3;

For additional information on table slicing, see "SLICES Table Option for MariaDB Xpand".

REPLICAS Table Option

The REPLICAS table option configures the number of replicas that are maintained for the table. MariaDB Xpand maintains multiple replicas of each slice to provide fault tolerance and high availability, and to ensure reads are balanced. By default, 2 replicas are maintained for each slice.

This statement creates a table with 3 replicas:

CREATE TABLE customers (
   customer_id BIGINT AUTO_INCREMENT NOT NULL,
   customer_name VARCHAR(500) NOT NULL,
   customer_email VARCHAR(200),
   PRIMARY KEY(customer_id)
) REPLICAS = 3;

To maintain a complete copy of the table on every node, use REPLICAS = ALLNODES:

CREATE TABLE customers (
   customer_id BIGINT AUTO_INCREMENT NOT NULL,
   customer_name VARCHAR(500) NOT NULL,
   customer_email VARCHAR(200),
   PRIMARY KEY(customer_id)
) REPLICAS = ALLNODES;

As a result, every slice in the hq_sales.customers table will have a replica stored on every node of the MariaDB Xpand cluster.

For additional information on table replicas, see "REPLICAS Table Option for MariaDB Xpand".

CHARACTER SET and COLLATE Table Options

The CHARACTER SET and COLLATION table options configure the default character set and collation for the table. Character sets define which characters can be used to store information in a string column. A collation is a set of rules for sorting and comparing character sets.

If a default character set and collation are not defined for a newly created table, the database's default character set and collation are applied to the table.

To set a different character set and/or collation for a new table:

CREATE TABLE customers (
   customer_id BIGINT AUTO_INCREMENT NOT NULL,
   customer_name VARCHAR(500) NOT NULL,
   customer_email VARCHAR(200),
   PRIMARY KEY(customer_id)
) DEFAULT CHARACTER SET = 'utf8mb4', DEFAULT COLLATE = 'utf8mb4_unicode_ci';

The DEFAULT reserved word is optional. This is the equivalent statement:

CREATE TABLE customers (
   customer_id BIGINT AUTO_INCREMENT NOT NULL,
   customer_name VARCHAR(500) NOT NULL,
   customer_email VARCHAR(200),
   PRIMARY KEY(customer_id)
) CHARACTER SET = 'utf8mb4', COLLATE = 'utf8mb4_unicode_ci';

You can set a different character set and/or collation for a specific column within a new table:

CREATE TABLE customers (
   customer_id BIGINT AUTO_INCREMENT NOT NULL,
   customer_name VARCHAR(500) CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci' NOT NULL,
   customer_email VARCHAR(200) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci',
   PRIMARY KEY(customer_id)
);

Create In-Memory Tables

To create an in-memory table, MariaDB Xpand uses skip lists data structure. Specify CONTAINER = SKIPLIST option to create an in-memory table:

CREATE TABLE customers (
   customer_id BIGINT AUTO_INCREMENT NOT NULL,
   customer_name VARCHAR(500) NOT NULL,
   customer_email VARCHAR(200),
   PRIMARY KEY(customer_id)
) CONTAINER = SKIPLIST;

You can also use an equivalent ENGINE = MEMORY option to create an in-memory table:

CREATE TABLE customers (
   customer_id BIGINT AUTO_INCREMENT NOT NULL,
   customer_name VARCHAR(500) NOT NULL,
   customer_email VARCHAR(200),
   PRIMARY KEY(customer_id)
) ENGINE = MEMORY;

Multiple Table Options

You can use multiple options at once while creating a table. For example, this statement creates an in-memory table with 3 slices, 3 replicas, and specific character sets and collations for customer_name and customer_email columns:

CREATE TABLE customers (
   customer_id BIGINT AUTO_INCREMENT NOT NULL,
   customer_name VARCHAR(500) CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci' NOT NULL,
   customer_email VARCHAR(200) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci',
   PRIMARY KEY(customer_id)
) SLICES = 3, REPLICAS = 3, ENGINE = MEMORY;

Compatibility Options

MariaDB Xpand ignores the following table options to allow easy migrations from InnoDB:

• KEY_BLOCK_SIZE

• ROW_FORMAT

• TYPE

These options are accepted, but ignored by the parser.

CREATE TABLE ... LIKE

Use the LIKE clause to create an empty table with the same definition as an existing table, including columns, indexes, and table options.

To create a similar table in the selected database:

CREATE TABLE invoices_archive LIKE invoices;

To create a similar table in another database:

CREATE TABLE db2.invoices LIKE db1.invoices;

The CREATE TABLE ... LIKE statement does not create foreign key definitions, as well as DATA DIRECTORY or INDEX DIRECTORY table options specified on the original table.

CREATE TABLE ... SELECT

You can create a table containing data from other tables returned by the SELECT query:

CREATE TABLE daily_report_invoices
   SELECT invoice_id, branch_id, invoice_total
      FROM invoices
      WHERE DATE(invoice_date) = CURDATE();

Columns will be created in the table for each column returned by the SELECT query.

With CREATE TABLE ... SELECT, columns using the following data types have special behavior:

• BIT columns are created as BIT(64)

• DECIMAL columns are created as VDECIMAL

• ENUM and SET columns are created as VARCHAR(256)

CREATE TEMPORARY TABLE

Temporary tables are dropped when the session ends. Temporary table names are specific to the session and do not conflict with other temporary tables from other sessions, even if they share the same name.

To create a temporary table that is only available to the current session, use the TEMPORARY keyword.

CREATE TEMPORARY TABLE customers (
   customer_id BIGINT AUTO_INCREMENT NOT NULL,
   customer_name VARCHAR(500) NOT NULL,
   customer_email VARCHAR(200),
   PRIMARY KEY(customer_id)
);