create table --create table

Use the "create table" syntax to create a table.

create table table name (
  Field definition 1,
  Field definition 2,
  Field definition n
) Options;

This is a sample to create a book classification "book_category" table with "create table" syntax.

create table book_category (
  id int primary key auto_increment,
  name varchar (150) not null default''''
) engine = InnoDB charset = utf8mb4;

What are the precautions when creating a table?

Here are some things to keep in mind when creating a table.

Database table and field name naming conventions

Since I often use databases when actually developing Web services, I created a topic for naming conventions for database table names and field names in Mojolicious startup.

Database table and field name naming conventions

How to make a portable "create table"?

MariaDB has the concept of a database engine, but the database engine used for Web service development is InnoDB. InnoDB is a transaction-supported database engine. Specify this as an option.

In MariaDB, the character code setting that correctly expresses UTF-8 is "utf8mb4". "Utf8" has only 3 bytes and cannot represent Unicode which requires 4 bytes. Specify "utf8mb4" that can be expressed in 4 bytes. Older MariaDB doesn't support utf8mb4, but getting started with MariaDB assumes a MariaDB version or later that supports utf8mb4.

If you use the "not null" constraint, specify default. This is to avoid this because the table creation will fail if default is not set depending on the MariaDB settings.

Associated Information