1. [Mandatory] Do not use COUNT(column_name) or COUNT(constant_value) in place of COUNT(*). COUNT(*) is SQL92 defined standard syntax to count the number of rows. It is not database specific and has nothing to do with NULL and non-NULL.

Note: COUNT(*) counts NULL row in, while COUNT(column_name) does not take NULL valued row into consideration.

2. [Mandatory] COUNT(distinct column) calculates number of rows with distinct values in this column, excluding NULL values. Please note that COUNT(distinct column1, column2) returns 0 if all values of one of the columns are NULL, even if the other column contains distinct non-NULL values.

3. [Mandatory] When all values of one column are NULL, COUNT(column) returns 0, while SUM(column) returns NULL, so pay attention to NullPointerException issue when using SUM().

Positive example: NPE issue could be avoided in this way:
SELECT IF(ISNULL(SUM(g)), 0, SUM(g)) FROM table;

4. [Mandatory] Use ISNULL() to check NULL values. Result will be NULL when comparing NULL with any other values.

Note:
  1) NULL<>NULL returns NULL, rather than false.
  2) NULL=NULL returns NULL, rather than true.
  3) NULL<>1 returns NULL, rather than true.

5. [Mandatory] When coding on DB query with paging logic, it should return immediately once count is 0, to avoid executing paging query statement followed.

6. [Mandatory] Foreign key and cascade update are not allowed. All foreign key related logic should be handled in application layer.

Note: e.g. Student table has student_id as primary key, score table has student_id as foreign key. When student.student_id is updated, score.student_id update is also triggered, this is called a cascading update. Foreign key and cascading update are suitable for single machine, low parallel systems, not for distributed, high parallel cluster systems. Cascading updates are strong blocked, as it may lead to a DB update storm. Foreign key affects DB insertion efficiency.

7. [Mandatory] Stored procedures are not allowed. They are difficult to debug, extend and not portable.

8. [Mandatory] When correcting data, delete and update DB records, SELECT should be done first to ensure data correctness.

9. [Recommended] IN clause should be avoided. Record set size of the IN clause should be evaluated carefully and control it within 1000, if it cannot be avoided.

10. [For Reference] For globalization needs, characters should be represented and stored with UTF-8, and be cautious of character number counting.

Note:
SELECT LENGTH(“轻松工作”); returns 12.
SELECT CHARACTER_LENGTH(“轻松工作”); returns 4.
Use UTF8MB4 encoding to store emoji if needed, taking into account of its difference from UTF-8.

11. [For Reference] TRUNCATE is not recommended when coding, even if it is faster than DELETE and uses less system, transaction log resource. Because TRUNCATE does not have transaction nor trigger DB trigger, problems might occur.

Note: In terms of Functionality, TRUNCATE TABLE is similar to DELETE without WHERE sub-clause.