Naming Conventions for Tables and Columns

SELECT word, reserved FROM information_schema.keywords WHERE word IN ('ORDER', 'GROUP', 'KEY', 'DESC', 'RANK', 'USER', 'STATUS', 'NAME', 'LIMIT', 'INDEX') ORDER BY word;

+--------+----------+
| WORD   | RESERVED |
+--------+----------+
| DESC   |        1 |
| GROUP  |        1 |
| INDEX  |        1 |
| KEY    |        1 |
| LIMIT  |        1 |
| NAME   |        0 |
| ORDER  |        1 |
| RANK   |        1 |
| STATUS |        0 |
| USER   |        0 |
+--------+----------+

CREATE TABLE rank (id INT);

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 (id INT)'

CREATE TABLE customers (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL);
CREATE TABLE orders (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, customer_id INT NOT NULL, created_at DATETIME NOT NULL);
CREATE TABLE invoices (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, customer_id BIGINT NOT NULL, created TIMESTAMP NULL);

SELECT column_name, GROUP_CONCAT(CONCAT(table_name, ':', column_type) ORDER BY table_name SEPARATOR '  ') AS definitions
FROM information_schema.columns
WHERE table_schema = DATABASE() AND column_name IN ('id', 'customer_id')
GROUP BY column_name
HAVING COUNT(DISTINCT column_type) > 1;

+-------------+-----------------------------+
| COLUMN_NAME | definitions                 |
+-------------+-----------------------------+
| customer_id | invoices:bigint  orders:int |
+-------------+-----------------------------+
1 row in set

SELECT table_name, column_name FROM information_schema.columns
WHERE table_schema = DATABASE() AND CAST(column_name AS BINARY) REGEXP '[^a-z0-9_]'
ORDER BY table_name, column_name;

ERROR 3995 (HY000): Character set 'binary' cannot be used in conjunction with 'utf8mb4_0900_ai_ci' in call to regexp_like.

CREATE TABLE members (
    id INT PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    age INT,
    UNIQUE (email),
    CHECK (age >= 0)
);
SELECT constraint_name, constraint_type FROM information_schema.table_constraints WHERE table_schema = DATABASE() AND table_name = 'members' ORDER BY constraint_type, constraint_name;

+-----------------+-----------------+
| CONSTRAINT_NAME | CONSTRAINT_TYPE |
+-----------------+-----------------+
| members_chk_1   | CHECK           |
| PRIMARY         | PRIMARY KEY     |
| email           | UNIQUE          |
+-----------------+-----------------+
3 rows in set