#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Science

MySQL Best Practices Every Developer Should Follow

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_nameSeq_in_indexColumn_nameNon_unique
PRIMARY1order_id0
fk_order_customer1customer_id1

(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;
QueryResult
SUM(amount) on the FLOAT table0.6000000163912773
SUM(amount) on the DECIMAL table0.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_idstatus
2pending
-- 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_idstatus
1paid
2cancelled
3cancelled
4pending

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_idorder_total
11499.00
32350.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

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

For the indexing side of these habits in more depth, read MySQL indexes explained and practical query optimisation techniques.

Launch Your Data Science Career

Learn MySQL, Python, Machine Learning and AI with Uncodemy's Data Science Training in Noida — hands-on projects, industry mentors and Dedicated Placement Support.

Enroll in Data Science Course →