Beamtic's logo

Share via:

SQL: Create Table

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

97 views

Edited: 2020-08-06 14:57

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.

Comments

  1. HTTP headers are not case-sensitive, so we are free to convert them to all-lowercase in our applications.
  2. In-app browsers do some things a little differently than normal browsers, and that might result in 500 errors on your server, among other interesting things.
  3. Short explanation of the HTTP range header.
  4. Learn why 403 is the most suitable status code for failed log in attempts.
  5. How to block unused URL parameters (non-existent) in PHP web applications.

More in: Web development