A new developer runs UPDATE orders SET status = 'cancelled', forgets the WHERE clause, and every order in the table is cancelled. A billing report shows ₹0.6000000163912773 instead of ₹0.60. A login form lets someone type ' OR '1'='1 and walk in. None of these are exotic bugs. They are the everyday mistakes that a handful of habits prevent. This page lists those habits, each with SQL you can run on MySQL 8.
1. Design tables with a primary key and clear names
Every InnoDB table should have an explicit primary key. InnoDB stores rows in primary-key order (the clustered index), so without one it creates a hidden row ID you cannot use in queries. Pick one naming style and keep it: lowercase snake_case, singular or plural table names (but not both), and names for constraints so error messages tell you what broke.
CREATE TABLE customer (
customer_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL,
city VARCHAR(60) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uq_customer_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE customer_order (
order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
customer_id INT UNSIGNED NOT NULL,
order_total DECIMAL(10,2) NOT NULL,
status ENUM('pending','paid','cancelled') NOT NULL DEFAULT 'pending',
ordered_at DATETIME NOT NULL,
CONSTRAINT fk_order_customer FOREIGN KEY (customer_id)
REFERENCES customer (customer_id)
) ENGINE=InnoDB;
Use utf8mb4, not the old utf8 (which is a 3-byte alias and cannot store emoji or some scripts). In MySQL 8 the server default is already utf8mb4 with the utf8mb4_0900_ai_ci collation, but stating it in the DDL keeps the table correct even on a server with a different default.
2. Index every foreign key (and check that it exists)
Joins and ON DELETE checks run on foreign-key columns, so they need an index. InnoDB creates one automatically when no usable index exists, and you can confirm it:
SHOW INDEX FROM customer_order;
| Key_name | Seq_in_index | Column_name | Non_unique |
|---|---|---|---|
| PRIMARY | 1 | order_id | 0 |
| fk_order_customer | 1 | customer_id | 1 |
(Output trimmed to four columns.) If you later add a composite index that starts with customer_id, MySQL can use that instead. Do not add indexes on every column, though: each index slows down INSERT and UPDATE and uses disk. Index the columns that appear in WHERE, JOIN and ORDER BY of your frequent queries.
3. Store money in DECIMAL, never FLOAT
FLOAT and DOUBLE are approximate binary types. They are fine for sensor readings, wrong for rupees. Here is the same data in both types:
CREATE TABLE price_float (id INT PRIMARY KEY, amount FLOAT);
CREATE TABLE price_dec (id INT PRIMARY KEY, amount DECIMAL(10,2));
INSERT INTO price_float VALUES (1,0.10),(2,0.20),(3,0.30);
INSERT INTO price_dec VALUES (1,0.10),(2,0.20),(3,0.30);
SELECT SUM(amount) FROM price_float;
SELECT SUM(amount) FROM price_dec;
SELECT * FROM price_float WHERE amount = 0.1;
| Query | Result |
|---|---|
SUM(amount) on the FLOAT table | 0.6000000163912773 |
SUM(amount) on the DECIMAL table | 0.60 |
The last query returns Empty set: the stored FLOAT is not exactly 0.1, so an equality test fails. With DECIMAL(10,2) both problems disappear.
4. Preview with SELECT, then change inside a transaction
Before any UPDATE or DELETE, run the same WHERE clause as a SELECT. Then make the change inside a transaction so you can still roll back. Sample rows:
INSERT INTO customer (full_name, email, city) VALUES
('Aarav Mehta', 'aarav@example.com', 'Pune'),
('Sana Khan', 'sana@example.com', 'Lucknow'),
('Rohit Das', 'rohit@example.com', 'Kolkata');
INSERT INTO customer_order (customer_id, order_total, status, ordered_at) VALUES
(1, 1499.00, 'paid', '2026-10-02 10:15:00'),
(2, 899.50, 'pending', '2026-10-03 18:40:00'),
(1, 2350.00, 'cancelled', '2026-10-05 09:05:00'),
(3, 640.00, 'pending', '2026-10-06 21:30:00');
-- step 1: what will be touched?
SELECT order_id, status FROM customer_order
WHERE status = 'pending' AND ordered_at < '2026-10-06';
| order_id | status |
|---|---|
| 2 | pending |
-- step 2: change it, check it, then decide
START TRANSACTION;
UPDATE customer_order SET status = 'cancelled'
WHERE status = 'pending' AND ordered_at < '2026-10-06'; -- 1 row affected
SELECT order_id, status FROM customer_order ORDER BY order_id;
COMMIT; -- or ROLLBACK if the result looks wrong
| order_id | status |
|---|---|
| 1 | paid |
| 2 | cancelled |
| 3 | cancelled |
| 4 | pending |
If the "rows affected" count is not what the preview showed, stop and ROLLBACK. In the mysql client you can also start it with --safe-updates, which refuses an UPDATE or DELETE that has no key-based WHERE or LIMIT. MySQL Workbench has the same "safe updates" option turned on by default.
5. Never build SQL by joining strings
Application code should send values as parameters, not paste them into the query text. Every driver supports this (? placeholders in JDBC, %s in Python's mysql-connector, named parameters in PDO). MySQL's own prepared statements show the idea:
SET @cid = 1;
PREPARE stmt FROM
'SELECT order_id, order_total FROM customer_order WHERE customer_id = ?';
EXECUTE stmt USING @cid;
DEALLOCATE PREPARE stmt;
| order_id | order_total |
|---|---|
| 1 | 1499.00 |
| 3 | 2350.00 |
The value travels separately from the SQL, so a quote character in user input can never change the query. This is the single most important defence against SQL injection; the MySQL security guide covers the rest (least-privilege users, TLS, password policies).
6. Ask for only what you need
- List columns instead of
SELECT *. Fewer bytes travel, and a covering index can answer the query without touching the table (EXPLAIN showsUsing index). - Add
LIMITwhile exploring a big table. - Do not wrap an indexed column in a function:
WHERE YEAR(ordered_at) = 2026cannot use an index onordered_at, whileWHERE ordered_at >= '2026-01-01' AND ordered_at < '2027-01-01'can. - Check plans with EXPLAIN before and after you add an index.
7. Treat schema changes and backups as code
Keep every CREATE and ALTER in numbered migration files (Flyway, Liquibase, Laravel or Django migrations, or plain V001__create_customer.sql files) inside Git. Nobody types DDL directly on production. For backups, schedule mysqldump --single-transaction or a physical backup tool, and restore a copy on a test server regularly. A backup you have never restored is only a hope; the mysqldump backup and restore guide walks through both steps.
Common mistakes this list prevents
- Tables with no primary key, which break replication tools and make duplicate rows hard to remove.
- Using
VARCHAR(255)for everything, including dates and numbers. Pick real types; see the MySQL data types guide. - Running the application as
root. Create a user with only the privileges the app needs. - Long transactions left open in a client tab, which hold locks and block other sessions.
For the indexing side of these habits in more depth, read MySQL indexes explained and practical query optimisation techniques.