Posted: March 11, 2021

Column definitions

Columns have a data type, along with some optional flags. MariaDB provides a full reference, but the most commonly used data types include:

INT (11)
BIGINT(20)
VARCHAR(65535)
TEXT
LONGTEXT
DATE
DATETIME
ENUM

The optional flags are:

AUTO_INCREMENT
PRIMARY KEY
DEFAULT ('value')
NOT NULL
UNSIGNED

To create a standard id column:

ALTER TABLE users ADD id INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY UNSIGNED;

Case sensitivity

You can type alter table et al in lowercase, but I use uppercase to distinguish the MYSQL COMMANDS from the variable names.

Variable names are case sensitive - MySQL will create them using the case you choose.

Create tables

CREATE TABLE IF NOT EXISTS users(
	id INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY UNSIGNED,
	other_column VARCHAR(8) DEFAULT 'myvalue',
	required_column VARCHAR(255) NOT NULL);

The entire command can be placed onto a single line - it’s only split here for readability.

Modify an existing column

ALTER TABLE users MODIFY other_column VARCHAR(255) DEFAULT 'Thiswouldhavebeentruncated';

Add new columns

ALTER TABLE users ADD created DATE;

For multiple columns:

ALTER TABLE users
	ADD created DATE,
	ADD username VARCHAR(128),
	ADD foreign_id INT(11);

Remove a column

ALTER TABLE users DROP COLUMN created;

Delete all data from a table

TRUNCATE users;

Delete an entire table (not just the data)

DROP TABLE users;

Switch databases

USE databasename;

Insert data

If you’re going to insert a value for every single column in the table, you can just type the values in order:

INSERT INTO tablename VALUES ('value1', 733, '2021-03-05 14:32:16');

If your table has a lot of columns and you only need to fill a few, you can specify the columns to fill:

INSERT INTO tablename (some_column, other_column, date) VALUES ('value1', 733, '2021-03-05 14:32:16');

Table encodings

It’s important to use the right encoding type for text in MySQL. Using the wrong encoding type will result in truncated or invalid data. Most notably, emojis and non-Latin character sets will fail with MySQL defaults.

The best encoding and collation set to use today is utf8mb4 and utf8mb4_unicode_ci: stackoverflow.com

To show the existing encoding on a table:

SHOW FULL COLUMNS FROM tablename;

To modify the encoding on an entire table:

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

For a single column:

ALTER TABLE tablename MODIFY columname VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;