MySQL development specifications

1. The database name, table name, and field name must be separated by lowercase letters and underlines. A) MySQL has the configuration parameter lower_case_table_names, which cannot be changed dynamically. The default value is 0 in linux. That is, the database and table names are stored in actual conditions and are case sensitive. If it is 1, it is stored in lower case and case insensitive. If it is 2, it is stored in actual conditions, but compared in lower case. B) If

1. The database name, table name, and field name must be separated by lowercase letters and underlines. A) MySQL has the configuration parameter lower_case_table_names, which cannot be changed dynamically. The default value is 0 in linux. That is, the database and table names are stored in actual conditions and are case sensitive. If it is 1, it is stored in lower case and case insensitive. If it is 2, it is stored in actual conditions, but compared in lower case. B) If

1. The database name, table name, and field name must be separated by lowercase letters and underlines.

A) MySQL has the configuration parameter lower_case_table_names, which cannot be changed dynamically. The default value is 0 in linux. That is, the database and table names are stored in actual conditions and are case sensitive. If it is 1, it is stored in lower case and case insensitive. If it is 2, it is stored in actual conditions, but compared in lower case.

B) if the case is mixed, multiple tables such as abc, Abc, and ABC may coexist, which may easily lead to confusion.

C) Field names are case-sensitive, but they are not used in actual use. That is, two fields with the same name but different cases cannot be created.

D) in order to unify the specification, the database name, table name, and field name should use lower-case letters.

2. The database name, table name, and field name cannot exceed 32 characters.

The database name, table name, and field name can contain a maximum of 64 characters. To ensure consistency, ease of identification, and reduce transmission volumes, it is not allowed to exceed 32 characters.

3. Use the INNODB Storage engine.

The INNODB engine is a default engine introduced by MySQL and later versions. It supports transactions and row-level locks, providing better data recovery and concurrent performance, at the same time, INNODB provides better support for multi-core, large memory, SSD and other hardware, and supports hot data backup. Therefore, INNODB has obvious advantages over MyISAM.

4. Reserved MySQL words are not allowed for the database name, table name, and field name.

When the database name, table name, field name, and other attributes contain reserved words, the SQL statement must reference the attribute name in reverse quotation marks, this makes writing SQL statements and escaping variables in SHELL scripts very complicated.

5. Partition tables are prohibited.

Partition tables have strict requirements on partition keys. When a partition table becomes larger, it becomes more difficult to execute DDL, SHARDING, and single table recovery. Therefore, you are not allowed to use partition tables. We recommend that you manually SHARDING the table on the business side.

6. We recommend that you use UNSIGNED to store non-negative values.

If the number of bytes is the same, the value range of the non-negative storage is greater. For example, TINYINT is signed as-128-127, and the unsigned value is 0-255.

7. int unsigned is recommended for IPV4 storage.

The unsinged int storage IP address occupies 4 bytes, and CHAR (15) occupies 15 bytes. In addition, the computer processes the integer type faster than the string type. Use int unsigned instead of CHAR (15) to store IPV4 addresses. Use the MySQL functions inet_ntoa and inet_aton for conversion. Currently, there is no conversion function for IPv6 addresses. You need to use DECIMAL or two BIGINT types for storage.

For example:

SELECT INET_ATON ('2017. 207.224.40 '); 209

SELECT INET_NTOA (3520061480); 209.207.224.40

8. TINYINT is strongly recommended to replace the ENUM type.

When you need to modify or add enumerated values for the ENUM type, you need to perform online DDL statements at a high cost. If the ENUM column value contains numeric values, the default values may be confused.

9. Use VARBINARY to store case-sensitive variable-length strings or binary content.

VARBINARY is case-sensitive by default. It does not have a character set and is fast.

10. The INT type occupies 4 bytes of storage. For example, INT (4) represents only four characters in width and does not represent the storage length.

The number after the value type brackets only indicates the width and has no relationship with the storage range. For example, if INT (3) is used, three digits are displayed by default, and spaces are filled. If the value is exceeded, the data is displayed normally, python and java clients do not have this function.

11. Use DATETIME and TIMESTAMP separately. The YEAR type is used for storage. The storage DATE uses the DATE type. We recommend that you use the TIMESTAMP type for storage time (accurate to seconds.

DATETIME and TIMESTAMP are precise to seconds, and TIMESTAMP is preferred, because TIMESTAMP only has four bytes, while DATETIME8 bytes. At the same time, TIMESTAMP has the features of automatic assignment and automatic update. Note: In versions 5.5 and earlier, if a table has multiple timestamp columns, only one column can be automatically updated.

How to use the automatic value assignment attribute of TIMESTAMP?

A) Automatic initialization and automatic UPDATE: column1 timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

B) Only automatic initialization: column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP

C) automatic UPDATE. The initialization value is 0: column1 timestamp default 0 on update CURRENT_TIMESTAMP

D) The initialization value is 0: column1 timestamp default 0

12. All fields are defined as not null.

A) For each row of a table, each NULL column requires additional space to identify.

B) Tree B indexes do not store NULL values. Therefore, if the index field can be NULL, the index efficiency will decrease.

C) we recommend that you use 0, special value, or NULL string instead of NULL value.

13. Split fields with large fields and low access frequency into separate tables to separate cold and hot data.

It is conducive to the effective use of the cache, prevents reading useless cold data, reduces disk IO, and ensures that hot data stays in the memory to increase the cache hit rate.

14. Do not store plaintext passwords in the database.

Use encrypted strings to store passwords, make sure they cannot be decrypted, and add random strings to salt to ensure password security. Prevent the database data from being obtained by internal staff or hackers, and use dictionary attacks or other methods to Crack user passwords.

15. The table must have a primary key. We recommend that you use the UNSIGNED auto-incrementing column as the primary key.

If a table does not have a primary key, INNODB sets hidden primary key columns by default. Tables without a primary key are very difficult to locate data rows and reduce the efficiency of Row-based replication.

16. Disable redundant indexes.

Indexing is a double-edged sword that increases the maintenance burden and increases the I/O pressure. (A, B, c), (a, B), the latter is a redundant index. Prefix indexes can be used to accelerate and reduce the maintenance burden.

17. Duplicate indexes are prohibited.

Primary key a; uniq index a; duplicate indexes increase the maintenance burden and occupy disk space without any benefit.

18. Do not create an index on the low base column, such as "gender ".

In most cases, the exact search of index creation on low base columns has no advantage over full table scanning without index creation, and increases the I/O burden.

19. Use the covered index properly to reduce IO and avoid sorting.

Covering indexes, you can obtain all required fields from the indexes. This avoids secondary searches and IO savings. In the INNODB Storage engine, secondary index (non-primary key index, also known as secondary index and secondary index) does not directly store row addresses, but stores primary key values. If you want to query the data columns not included in secondary index, You need to first find the primary key value through secondary index, and then query other data columns through the primary key. Therefore, you need to query it twice. Overwrite indexes can obtain all the required data in an index, so the efficiency is high. Primary Key query is a natural overwriting index. For example, SELECT email, uid FROM user_email WHERE uid = xx. If the uid is not the primary key, you can add the index as index (uid, email) as appropriate to improve performance.

20. replace OR with IN. The number of IN values in SQL statements should be less than 1000.

IN is a range query. MySQL sorts the list values of IN internally and finds the list values, which is more efficient than OR.

21. The table Character Set uses UTF8. If necessary, you can apply to use the UTF8MB4 character set.

A) The UTF8 Character Set stores three bytes of Chinese characters and one byte of English characters.

B) UTF8 is unified and universal, and there is no risk of garbled code During transcoding.

C) if you need to store emojis such as EMOJ, you can apply to use the UTF8MB4 character set.

22. Replace UNION with union all.

Union all does not need to sort the result set.

23. order by rand () is forbidden ().