CertGrid CertGrid
Hands-on Lab·MySQL

MySQL Databases and Tables

Create a database, name its character set explicitly, then read the table back with `SHOW CREATE TABLE` - which shows every default MySQL filled in that you did not type, and is the only honest answer to what a table actually is.

Schemas and Data Guide 6 of 45 Beginner

Written against the versions above. utf8mb4 and utf8mb4_0900_ai_ci are the 8.0+ defaults. On 5.7 the default was latin1, which is exactly why naming it explicitly is a habit worth having.

Every command on this page ran on db-a01.
Server NameIP AddressOSRolesCPURAMHDD
db-a01192.168.0.81Ubuntu 26.04 LTSPrimary / Source / Replica Set Member 12 Core4 GB50 GB

Before you start

  1. Create a database, and say what it is for

    CREATE DATABASE on its own would work. Naming the character set and collation is the habit worth forming: it is metadata every table inherits, and changing it later means rewriting every table in the schema.

    utf8mb4 is real, four-byte Unicode. MySQL's older utf8 is three bytes and cannot store an emoji or several CJK characters - a genuine trap, and the reason the name has a mb4 on the end.

    bash Example session 
    sudo mysql -e "CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci"sudo mysql --table -e "SHOW DATABASES"+--------------------+| Database           |+--------------------+| appdb              || information_schema || mysql              || performance_schema || sys                |+--------------------+

    Expected resultNo output from the create, then appdb alongside the four system schemas.

    Success conditionYou have a database whose encoding you chose rather than inherited.

  2. Read back what the server actually stored

    SHOW CREATE DATABASE is the server's own account of the object, not an echo of what you typed. Get into the habit of trusting it over your memory of the statement.

    \G prints it one field per line, which matters here because the create statement is long.

    bash Example session 
    sudo mysql -e "SHOW CREATE DATABASE appdb\G"*************************** 1. row ***************************       Database: appdbCreate Database: CREATE DATABASE `appdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */

    Expected resultA CREATE DATABASE line carrying utf8mb4 and utf8mb4_0900_ai_ci.

    Success conditionYou can confirm an object's real definition rather than assuming it.

  3. Create a table with the column types you meant

    Five columns, each chosen deliberately.

    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY is the standard surrogate key - unsigned because there is no such thing as customer minus four. VARCHAR(80) stores up to 80 characters, not bytes. DECIMAL(10,2) for money, never FLOAT: decimal is exact, and a float cannot represent 0.10 precisely, which is how a balance drifts by a penny per transaction. TIMESTAMP ... DEFAULT CURRENT_TIMESTAMP lets the server fill the value so every row is stamped by the same clock.

    NOT NULL on name and email is a decision, and the next guide shows the server enforcing it.

    bash 
    sudo mysql appdb -e "CREATE TABLE customers (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(80) NOT NULL, email VARCHAR(120) NOT NULL UNIQUE, credit DECIMAL(10,2) NOT NULL DEFAULT 0.00, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP)"

    Expected resultNo output. In SQL that is success.

    Success conditionThe table exists with types chosen for what they hold.

  4. See everything you did not type

    This is the step to slow down on. SHOW CREATE TABLE returns the full definition including every default the server supplied.

    Look for what appeared on its own: ENGINE=InnoDB, the table's own DEFAULT CHARSET and COLLATE inherited from the database, and a named UNIQUE KEY on email - you wrote the word UNIQUE inline and MySQL turned it into a named index, because in InnoDB a unique constraint is an index.

    That last point matters later: it is why the duplicate check in the next guide is fast, and why the same index serves lookups by email.

    bash Example session 
    sudo mysql appdb -e "SHOW CREATE TABLE customers\G"*************************** 1. row ***************************       Table: customersCreate Table: CREATE TABLE `customers` (  `id` int unsigned NOT NULL AUTO_INCREMENT,  `name` varchar(80) NOT NULL,  `email` varchar(120) NOT NULL,  `credit` decimal(10,2) NOT NULL DEFAULT '0.00',  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  PRIMARY KEY (`id`),  UNIQUE KEY `email` (`email`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

    Expected resultThe full definition with ENGINE=InnoDB, a PRIMARY KEY, and a named UNIQUE KEY on email.

    Success conditionYou know what the server filled in for you and why the unique key is an index.

  5. The short form, for when you only need the shape

    DESCRIBE is the quick view: column, type, nullability, key, default, extra.

    Read the Null column against what you declared. id, name and email say NO; nothing else was constrained, so credit and created_at carry their defaults instead. Extra shows auto_increment on id and DEFAULT_GENERATED on the timestamp.

    Use DESCRIBE to remind yourself of a shape and SHOW CREATE TABLE when the details matter.

    bash Example session 
    sudo mysql --table appdb -e "DESCRIBE customers"+------------+---------------+------+-----+-------------------+-------------------+| Field      | Type          | Null | Key | Default           | Extra             |+------------+---------------+------+-----+-------------------+-------------------+| id         | int unsigned  | NO   | PRI | NULL              | auto_increment    || name       | varchar(80)   | NO   |     | NULL              |                   || email      | varchar(120)  | NO   | UNI | NULL              |                   || credit     | decimal(10,2) | NO   |     | 0.00              |                   || created_at | timestamp     | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |+------------+---------------+------+-----+-------------------+-------------------+

    Expected resultFive rows with id as PRI and email as UNI.

    Success conditionYou can read a table's shape at a glance.

Troubleshooting

Official sources

← All MySQL guides