Naming, storage engines and character sets
35 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)Every table carries four choices besides its columns: its name, the names of its columns, the storage engine that keeps its rows on disk, and the character set and collation that decide which letters it can store and how it compares them. Good defaults for all four save you from strange errors later.
snake_case names, singular or plural tables
snake_case means lowercase words joined by underscores, such as order_items or unit_price. It is the usual style for MySQL names: easy to read, no quoting needed, and the same on every computer.
snake_case means lowercase words joined by underscores: order_items, joined_on, unit_price. It is the usual style for MySQL names because names are easy to read, need no quoting, and look the same on every computer.
Example 1 · A clean, consistently named table
CREATE TABLE hostel_allotments ( allotment_id INT AUTO_INCREMENT PRIMARY KEY, student_id INT, room_no SMALLINT, allotted_on DATE, monthly_rent DECIMAL(8,2), is_ac BOOLEAN);
DESCRIBE hostel_allotments;+--------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+----------------+ | allotment_id | int | NO | PRI | NULL | auto_increment | | student_id | int | YES | | NULL | | | room_no | smallint | YES | | NULL | | | allotted_on | date | YES | | NULL | | | monthly_rent | decimal(8,2) | YES | | NULL | | | is_ac | tinyint(1) | YES | | NULL | | +--------------+--------------+------+-----+---------+----------------+
What this code does
- Plural table name,
snake_caseeverywhere, the id named after the table, and names that say what they hold:allotted_onis clearly a date,is_acclearly yes/no. student_iduses the same name asstudents.student_idin the practice database, so anyone can guess that the two columns are linked.
A few rules that keep a schema readable:
- Use only lowercase letters, digits and
_; start with a letter. MySQL allows up to 64 characters. - Say what the value is:
order_date, notodordate1. Add the unit when it helps:weight_kg,duration_min. - Name an id after its table:
customer_idincustomers, and the same name in every table that points to it. - Pick plural (
customers) or singular (customer) for table names and use it everywhere. This course uses plural, like the practice database.
Why lowercase? On Windows and macOS, MySQL treats Students and students as the same table by default; on Linux servers, where most databases run, they are two different tables. Lowercase names behave the same everywhere.
Reserved words such as order and rank, and backticks
A reserved word is a word SQL itself uses, such as ORDER, GROUP, KEY, DESC or RANK. Using one as a plain name makes the statement fail, so choose a clearer name or quote it with backticks.
A reserved word is a word SQL itself uses, such as ORDER, GROUP, SELECT, KEY, DESC or RANK. Using one as a plain name confuses the parser: CREATE TABLE order (...) fails with error 1064, because MySQL reads order as the start of ORDER BY.
Example 2 · Reserved words need backticks
CREATE TABLE `order` ( `id` INT, `desc` VARCHAR(40), `group` CHAR(1));
INSERT INTO `order` VALUES (1, 'Notebook', 'A');SELECT `id`, `desc`, `group` FROM `order`;
SELECT desc FROM `order`;+------+----------+-------+ | id | desc | group | +------+----------+-------+ | 1 | Notebook | A | +------+----------+-------+ ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc FROM `order`' at line 1
What this code does
- With backticks,
order,descandgroupwork as names, and the first SELECT runs. - The second SELECT forgets the backticks around
descand fails with error 1064. This is why names such asorders,descriptionandgroup_codeare better.
Example 3 · RANK became reserved in MySQL 8.0
CREATE TABLE exam_toppers ( student VARCHAR(30), exam_rank INT);INSERT INTO exam_toppers VALUES ('Deepika', 1), ('Aditya', 2);SELECT * FROM exam_toppers ORDER BY exam_rank;
CREATE TABLE old_toppers ( student VARCHAR(30), rank INT);+---------+-----------+ | student | exam_rank | +---------+-----------+ | Deepika | 1 | | Aditya | 2 | +---------+-----------+ ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank INT )' at line 3
What this code does
exam_rankis clear and needs no quoting.- A column named
rankwas fine in MySQL 5.7 but fails with error 1064 from 8.0, whereRANKis a reserved word.
Backticks, the slanted quote on the key left of 1 on most keyboards, turn any word into a name: put them around the word order and the CREATE TABLE works, as Example 2 shows. But then every query must use the backticks too, forever, and someone will forget. The better fix is a clearer name: orders, group_name, description, exam_rank.
The list of reserved words grows with new versions. RANK became reserved in MySQL 8.0, so old tables with a column called rank broke after an upgrade. Some keywords are not reserved and work as names, such as user, name and status, but avoid the doubtful ones anyway.
Find the bug
Find the reserved word
Each script has one line that fails because of a reserved word.
Create a table for customer orders.
Round 1 of 2InnoDB: the storage engine you should use
A storage engine is the part of MySQL that stores a table's rows on disk and reads them back. InnoDB is the default and the one to use: it has transactions, crash recovery and foreign keys.
A storage engine is the part of MySQL that actually stores a table's rows on disk and reads them back. MySQL lets each table choose one, and InnoDB is the default since MySQL 5.5.
Example 4 · MyISAM ignores foreign keys
CREATE TABLE old_orders ( order_id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers (customer_id)) ENGINE = MyISAM;
INSERT INTO old_orders VALUES (1, 999);SELECT * FROM old_orders;
SELECT TABLE_NAME, ENGINEFROM information_schema.TABLESWHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ('orders', 'old_orders')ORDER BY TABLE_NAME;+----------+-------------+ | order_id | customer_id | +----------+-------------+ | 1 | 999 | +----------+-------------+ +------------+--------+ | TABLE_NAME | ENGINE | +------------+--------+ | old_orders | MyISAM | | orders | InnoDB | +------------+--------+
What this code does
- There is no customer 999, yet MyISAM accepted the order: it dropped the
FOREIGN KEYclause without an error or a warning. The InnoDB tableordersrefuses the same insert with error 1452 (a foreign key constraint fails). information_schema.TABLESis MySQL's built-in list of tables, andDATABASE()is the current database. The query shows each table's engine:ordersuses InnoDB, the default.
InnoDB gives you what the rest of this course relies on:
- Transactions: several changes succeed or fail together (the Transactions module).
- Crash recovery: after a power cut, committed data is still there.
- Foreign keys: links between tables are checked.
- Row-level locking: many users can change different rows at the same time.
The old engine MyISAM has none of these. Worst of all, it accepts a FOREIGN KEY clause and then silently ignores it. You will meet MyISAM only in old projects; for new tables, leave the engine out (InnoDB is chosen) or write ENGINE = InnoDB.
utf8mb4 and collations: why "Ravi" equals "ravi"
A character set is the list of characters a column can store, such as utf8mb4. A collation is the set of rules for comparing and sorting text, such as utf8mb4_0900_ai_ci, which ignores case and accents.
A character set is the list of characters a column can store and how they are turned into bytes. utf8mb4 covers all of Unicode: English, every Indian script, symbols such as ₹ and emoji, using 1 to 4 bytes per character.
Example 5 · Comparisons under the default collation
SELECT 'Ravi' = 'ravi' AS same_case_ignored, 'resume' = 'résumé' AS accent_ignored, 'Ravi' = 'ravi' COLLATE utf8mb4_bin AS exact_bytes;
CREATE TABLE app_signups ( email VARCHAR(100) UNIQUE);INSERT INTO app_signups VALUES ('Ravi.Kumar@example.com');INSERT INTO app_signups VALUES ('ravi.kumar@example.com');+-------------------+----------------+-------------+ | same_case_ignored | accent_ignored | exact_bytes | +-------------------+----------------+-------------+ | 1 | 1 | 0 | +-------------------+----------------+-------------+ ERROR 1062 (23000): Duplicate entry 'ravi.kumar@example.com' for key 'app_signups.email'
What this code does
- Under
utf8mb4_0900_ai_ci, case and accents are ignored, so the first two comparisons are true (1).COLLATE utf8mb4_binswitches to exact byte comparison for one expression, and then they differ (0). - The
UNIQUEemail column (Keys & Constraints covers UNIQUE) refuses the lowercase copy as a duplicate: good for emails, which are not case-sensitive in practice.
Example 6 · utf8mb3 refuses emoji; a case-sensitive column
CREATE TABLE coupon_codes ( code VARCHAR(20) COLLATE utf8mb4_0900_as_cs, discount TINYINT);INSERT INTO coupon_codes VALUES ('FEST50', 50), ('fest50', 5);SELECT code, discount, code = 'FEST50' AS matches_upper FROM coupon_codes ORDER BY discount;
CREATE TABLE fest_posts ( message VARCHAR(100)) CHARACTER SET utf8mb3;INSERT INTO fest_posts VALUES ('Tech fest starts now 🎉');+--------+----------+---------------+ | code | discount | matches_upper | +--------+----------+---------------+ | fest50 | 5 | 0 | | FEST50 | 50 | 1 | +--------+----------+---------------+ ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x8E\x89' for column 'message' at row 1
What this code does
- With
utf8mb4_0900_as_cs,FEST50andfest50are two different codes, andcode = 'FEST50'matches only the uppercase one. - The
utf8mb3table cannot store the 4-byte emoji and fails with error 1366. The bytes in the message are the emoji's UTF-8 code. Withutf8mb4, the default, the same insert works.
utf8mb4 is the default from MySQL 8.0. The older utf8 (really utf8mb3) stops at 3 bytes, so an emoji is refused with ERROR 1366 (HY000): Incorrect string value.
The MySQL 8.0 default collation is utf8mb4_0900_ai_ci: 0900 is the Unicode version of the rules, ai means accent-insensitive and ci means case-insensitive. So 'Ravi' = 'ravi' is true, 'resume' = 'résumé' is true, and a UNIQUE email column treats Ravi@x.com and ravi@x.com as the same value. That default is usually what people want for names, emails and searches. When case must matter, such as coupon codes or case-sensitive usernames, give that column a case-sensitive collation: utf8mb4_0900_as_cs (accent- and case-sensitive) or utf8mb4_bin (compare the exact bytes).
Predict the output
Predict the comparison
The default collation is utf8mb4_0900_ai_ci. What does this return?
CREATE DATABASE practice CHARACTER SET utf8mb4;. Tables and columns then inherit it.Next step
Check your understanding
11 quick questions