SQL: Create Table

How to create a new table in a SQL-compatible database like MySQL.

526 views

By. Jacob

Edited: 2021-02-05 23:44

The create table query can be used to create a new table in an existing database. Before you create a new table, it is important to think about the table layout, and which data types you need to use.

For example, if you are creating a table to contain articles posted to a blog, then you probably want to use the text data type for the body text; while for the title, description, url you can use varchar — but more about this later.

For MySQL, a simple create table query with a unique index can look like this:

create table blog_posts (
  title varchar(100) not null,
  description varchar(255) not null,
  url varchar(255) not null,
  ptime timestamp default current_timestamp,
  mtime timestamp default current_timestamp,
  unique (url)
);

This query will create a table that automatically inserts the current timestamp when a new blog article is created. The layout will look like this:

mysql> describe blog_posts;
+-------------+--------------+------+-----+-------------------+-------------------+
| Field       | Type         | Null | Key | Default           | Extra             |
+-------------+--------------+------+-----+-------------------+-------------------+
| title       | varchar(100) | NO   |     | NULL              |                   |
| description | varchar(255) | NO   |     | NULL              |                   |
| url         | varchar(255) | NO   | PRI | NULL              |                   |
| ptime       | timestamp    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| mtime       | timestamp    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-------------+--------------+------+-----+-------------------+-------------------+
5 rows in set (0.01 sec)

This table gives you a decent foundation to build your own blog CMS from; you can easily adjust it to your own needs using alter table queries.

Choosing datatypes

Note. A field will only take up the size of the data it contains, and not the maximum size of the column.

Choosing a suitable datatype for each column in a table is an important part of constructing your database tables; for many purposes, varchar and text will be sufficient. But, you should also know about other datatypes, and beware of some of the differences.

Here is a few string datatypes:

varchar	0-255 bytes (+1 byte to store the data) or 255-65535 bytes (+2 bytes to store the data)
text	65535 bytes
mediumtext	6.777.215 bytes — (16 MB)
longtext	4.294.967.295 bytes — (4 GB)

Both varchar and text are string datatypes. The varchar type is used for data of variable-length. The difference between the two is mainly that the text type is of fixed-length (65535 bytes), while the length of varchar can be adjusted as needed.

A varchar of length 0-255 takes up 1 byte + the size of the data; 255-65535 takes up 2 butes + size of the data.

If the largest row is bigger than 65535 bytes, you may want to use mediumtext or longtext instead.

Choosing datatypes

Tell us what you think: