MariaDB Insert

Summary: in this tutorial, you will learn how to use the MariaDB insert statement to insert a row into a table.

Introduction MariaDB insert statement

The insert statement allows you to add a new row to a table. The following shows the syntax of the insert statement:

insert into table_name(column_list)
values(value_list);
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the table that you want to insert data after the insert into keywords.
• Second, specify a list of columns of the table within parentheses after the table name.
• Third, specify a list of values in parentheses, which follows the values keyword. The number of values must be the same as the number of specified columns. In addition, the column and the value list must be in the same order.

Even though the column list is optional, you should always include it in the insert statement. It is a good programming practice.

If you skip any column of a table in the column list, you must ensure that the omitted column has a default value or an error will occur.

MariaDB uses the following value to insert into the column if you don’t specify the column in the column list:

• The next sequential integer if the column has auto_increment property.
• The NULL if the column is a nullable column
• The default value if the column explicitly specifies a default value.
• The generated value if the column is a generated column.

To insert multiple rows into a table using a single statement, see the insert multiple rows tutorial.

MariaDB insert statement examples

The following statement creates a new table called contacts to store the contact information:

create table contacts(
    id int auto_increment,
    first_name varchar(50) not null,
    last_name varchar(50) not null,
    full_name varchar(101) 
        as (concat(first_name, ' ', last_name)) virtual,
    phone varchar(100),
    contact_group varchar(50) default 'General',
    primary key(id)
);
Code language: SQL (Structured Query Language) (sql)

In the contacts table:

• The id column is an auto_increment column, therefore, MariaDB will automatically insert the next sequential integer if you don’t explicitly specify a value in the insert statement.
• The full_name is a generated column whose value is derived from the first name and last name columns.
• The contact_group column has a default value as 'General'. If you don’t explicitly specify a value when you insert or update this column, MariaDB will use this default value.

A) Simple MariaDB insert example

The following statement uses the insert statement to add a new row to the contacts table:

insert into contacts(first_name, last_name, phone)
values('John','Doe','(408)-934-2443');
Code language: SQL (Structured Query Language) (sql)

MariaDB issued the following message:

Affected rows: 1  Found rows: 0  Warnings: 0  Duration for 1 query: 0.000 sec.
Code language: SQL (Structured Query Language) (sql)

It means that one row has been inserted into the contacts table.

This query selects data from the contacts table:

select * from contacts;
Code language: SQL (Structured Query Language) (sql)

B) Using MariaDB insert to add a row to a table and return the inserted id example

The following query uses the insert statement to add a new row to the contacts table:

insert into contacts(first_name, last_name, phone, contact_group)
values('Jane','Doe','(408)-456-8934','Leads');
Code language: SQL (Structured Query Language) (sql)

To get the inserted id, you use the last_insert_id() function:

select last_insert_id();
Code language: SQL (Structured Query Language) (sql)

The following query returns rows from the contacts table:

select * from contacts;
Code language: SQL (Structured Query Language) (sql)

C) Using MariaDB insert statement and the default keyword example

If you specify a column that has a default value in the column list and you want to use its default value when inserting a new row into the table, you can use the default keyword in the value list. See the following example:

insert into contacts(first_name, last_name, phone, contact_group)
values('Roberto','carlos','(408)-242-3845',default);
Code language: SQL (Structured Query Language) (sql)

In this example, we explicitly include the contact_group in the column list and use the default keyword to instructs MariaDB to use the column’s default value.

MariaDB insert into set statement

MariaDB provides an alternative syntax for inserting a new row into a table using the set clause:

insert into table_name
set column1 = value1,
    column2 = value2,
    ...;
Code language: SQL (Structured Query Language) (sql)

In this syntax, you don’t have to put the columns and values in order.

Note that this syntax is specific to MariaDB and MySQL. Other database management systems may not support it.

The following example uses the insert into set statement to add a new row to the contacts table:

insert into contacts
set first_name = 'Jonathan',
    last_name = 'Van';	
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the MariaDB insert statement to insert a new row into a table.