Basic20 min read

MySQL Basics

Install MySQL, create databases and tables, understand data types, and run basic SQL.

Installation and First Connection

Install MySQL Community Server or use a managed service such as Amazon RDS or PlanetScale. The mysql client connects to the server for interactive SQL; applications connect via drivers using host, port, user, password, and database name.

Secure the initial root account immediately, disable remote root login in production, and store credentials in environment variables or a secrets manager rather than application source code.

Use utf8mb4 charset to support full Unicode including emoji
Default port is 3306; change only with matching firewall rules
Verify server version compatibility with your ORM and driver

mysql -u root -p
CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE shop;

Databases and Tables

A MySQL server hosts multiple databases (schemas). Tables define columns with types and constraints. Primary keys uniquely identify rows; auto-increment integers or UUIDs are common choices.

Use meaningful table and column names in snake_case or consistent camelCase. Document nullable vs required fields and default values in migrations so schema evolution stays team-visible.

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

MySQL Data Types

Choose types that match your data size and semantics. INT and BIGINT for integers, DECIMAL for money, VARCHAR for variable strings, TEXT for long content, JSON for semi-structured data, and DATETIME or TIMESTAMP for temporal values.

Avoid storing numbers in VARCHAR or using FLOAT for currency. TIMESTAMP stores UTC internally with timezone conversion on display depending on session settings; DATETIME stores literal values without timezone conversion.

ENUM and SET types exist but often complicate migrations—consider lookup tables instead
Use TINYINT(1) or BOOLEAN alias for true/false flags consistently
Size VARCHAR appropriately; oversized columns waste index space

Basic SQL Operations

INSERT adds rows, SELECT retrieves them, UPDATE modifies existing rows, and DELETE removes rows. Always use WHERE on UPDATE and DELETE unless you intentionally affect every row.

Use parameterized queries from application code to prevent SQL injection. Never concatenate user input into SQL strings.

INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada Lovelace');

SELECT id, email, name FROM users WHERE name LIKE 'A%';

UPDATE users SET name = 'Ada L.' WHERE id = 1;

DELETE FROM users WHERE id = 1;

Development Workflow

Manage schema changes with migration tools (Flyway, Liquibase, Prisma Migrate, Rails migrations) rather than manual ALTER in production. Keep migrations small, reversible where possible, and reviewed like application code.

Seed development databases with representative data volumes to catch slow queries before production. Use separate databases or schemas per environment.