~

SQL

This guide is not kind of guide which cover all cases. Just my little tricks when I work with SQL.

Primary key

Timestamp

Don't use MySQL type timestamp, use int64 then pass the timestamp (prefer UnixMilli) to avoid date, time, location complex conversion. 

[Go] time.Time -> t.UnixMilli() -> [Database] int64

JSON field

Index

You should use index for faster query, but not too much. Don't create index for every fields in table. Choose wisely!

For example, create index in MySQL:

CREATE INDEX idx_user_id
ON user_upload (user_id);

If create index inside CREATE TABLE, prefer INDEX to KEY: 

CREATE TABLE user_upload
(
    id INT(11) NOT NULL,
    user_id INT(11) NULL DEFAULT NULL,
    PRIMARY KEY (id),
    INDEX idx_user_id (user_id)
);

If use composite index, order is important, either both are DESC or ASC, do not mix: 

CREATE INDEX idx_user_id_created_at
ON user_upload (user_id, created_at);

-- Bad
SELECT *
FROM user_upload
ORDER BY user_id, created_at DESC;

-- Good
SELECT *
FROM user_upload
ORDER BY user_id, created_at;

-- Also good
SELECT *
FROM user_upload
ORDER BY user_id DESC, created_at DESC;

Use EXPLAIN to check if index is used or not:

UTF-8

TLDR with MySQL:

CREATE TABLE ekyc_approved
(
    id varchar(30) NOT NULL,
    PRIMARY KEY (id),
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

Be careful if you edit/cut string which has emoji before saving to database.

NULL

If compare with field which can be NULL, remember to check NULL for safety. 

-- field_something can be NULL

-- Bad
SELECT *
FROM table
WHERE field_something != 1

-- Good
SELECT *
FROM table
WHERE (field_something IS NULL OR field_something != 1)

Because NULL is not equal to anything, even NULL != NULL, we only can check with IS NULL or IS NOT NULL.

This is based on Kleene's TRUE-FALSE-UNKNOWN logic.

VARCHAR or TEXT

Prefer VARCHAR if you need to query and of course use index, and make sure size of value will never hit the limit. Prefer TEXT if you don't care, just want to store something.

LIMIT

Prefer LIMIT 10 OFFSET 5 to LIMIT 5, 10 to avoid misunderstanding.

Be super careful when migrate, update database on production and online!!!

Please read docs about online ddl operations before do anything online (keep database running the same time update it, for example create index, ...)

Heathcheck

Use SELECT 1 to check if database failed yet.

Tools

Pastebin

Show index of table: 

SELECT DISTINCT
    table_name,
    index_name
FROM information_schema.statistics;

Thanks


Source code is available on GitHub