Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the specifications of the mysql database, which may not be well understood by many people. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

Basic specification

[recommended] use InnoDB storage engine

[mandatory] UTF8 character set must be used without special requirements

[mandatory] data tables and data fields must be annotated in Chinese

[force] prohibit the use of stored procedures, views, triggers, Event. Application for evaluation under special circumstances

[forced] do not do operations in the database, cpu calculation must be moved to the business layer

Naming convention

[suggestion] English words and abbreviations with practical meaning are used in naming, and underscores are used to separate words.

[mandatory] naming can only use lowercase letters, numbers and underscores, and must start with an English letter, underscore is a separator, and cannot exceed 32 characters. Database object names should be as short as possible. Avoid using reserved words of MySQL

[mandatory] Common table name naming rules are function module prefix _ + tablename (login_users); temporary table: tmp prefix + tablename+ 8-bit time suffix (tmp_users_20170501); backup table: bak prefix + tablename+ 8-bit time suffix (bak_users_20170501); archive table naming rules: arch prefix + tablename+ archiving rules (arch _ users_2013) [force] fields with the same meaning between tables must have the same name, data type, length and unit must be the same.

[force] the index starts with idx_. The unique index starts with uq_idx, followed by the field name of the index, the column name composed of multiple words, and abbreviations that represent the meaning as much as possible, such as the combined index on the table member _ id and friend_id: the naming of the idx_ mid_fid, composite index should pay attention to the order of fields. If you create a combined index on field member_id and field user_id, you can name it idx _ uid_mid (userid, member_id)

Common conventions:

[recommended] sequence number column field: suffixed with id, for example: user_id represents user number

[recommended] Encoding field: suffixed with code. For example, cust_code represents customer coding.

[suggested] Boolean field: describe with "is_" prefix + field + adjective. For example, the column of members represented as enabled on the member table is named is_member_enabled. 0: no; 1: yes

[mandatory] status field: suffixed with "_ status" and preceded by business logic name. For example, user status can be named user_status, order status can be order_status, and so on

Table design specification (*)

[mandatory] Table design must have a table primary key, and the primary key cannot be provided to the external system, and the external system must use the business primary key, such as the business primary key design of the user table, such as the id table primary key, self-increasing, the table primary key can not provide xxx_id as the business primary key like the external system, using IdGenerater (id generation tool class generation, see attachment), can be provided to the external system, using bigint storage

The table must have a primary key. If you use auto_increment as the self-incrementing primary key, be careful not to set the starting value when exporting the initialization script.

[force] enumerated types use tinyint types

[mandatory] do not have too many fields in a single table, no more than 50 at most, and use as few character data types as possible

[force] date data (excluding hour minute and second), use int (11) to store (e.g., yyyy, yyyyMM, yyyyMMdd), time data (including hour minute and second), use datetime storage.

[mandatory] each table must contain two reserved fields: create_time (creation time), update_time (last modified time) creater varchar (50) (creator), and updater varchar (50) (modifier), set to a non-empty field property. These two fields do not contain additional business logic.

[force] each table sets the is_del (0 is not deleted, 1 is deleted) flag bit field, which is set to non-empty, and the default is 0. Physical deletion is not allowed in the production environment. Reconsideration of special table

[force] tables and columns must be defined with comment, and can accurately describe the meaning of tables and columns. Fields such as type and status must clearly give the meaning of each value; measurement fields such as money must give precise units of measurement; foreign key fields must clearly give the associated tables and fields.

[mandatory] if a JOIN field (connection key) is required, the field name, data type, length and unit must be absolutely consistent to avoid implicit conversion

[mandatory] it is forbidden to use TEXT and BLOB types (large text, large files and large photos are stored in the file system). Files can be placed on the file server, and only url is stored in the database.

[mandatory] enum,set is not recommended. Because they waste space, and the enumeration values are written dead, it is not convenient to change. Tinyint or smallint is recommended

[mandatory] if there is an additional field for business flow: business serial number

[mandatory] if you need to view the modification or rollback operation of multiple tables at a time, add the operation serial number.

[force] prohibit the creation of foreign key constraints, which are controlled by the application. Foreign keys will lead to table-to-table coupling, and both update and delete operations will involve associated tables, affecting the performance of sql and even causing deadlocks.

[force] none of the sort fields are allowed to be empty, and set the default value.

Field design specification

[mandatory] string types all use VARCHAR type, and it is recommended to use char for definite length, such as ID number, etc.

[mandatory] the use of TEXT and BLOB types is prohibited. More disk and memory space will be wasted, and unnecessary large field queries will eliminate hot data, resulting in a sharp decrease in memory hit rate and affect database performance.

The [recommended] field is defined as NOT NULL and provides a default value. The columns of null make index / index statistics / value comparisons more complex and more difficult to optimize for MySQL; more empty storage is required; only is null or is not null can be used, but not =, in, = '2017-02-15 color color-the correct way to write it is: SELECT uid FROM t_user WHERE day > = xxxfunc (' 2017-02-1500 is not null')

[mandatory] the use of OR conditions is prohibited. Use IN or UINON instead

[mandatory] large tables are prohibited from using JOIN queries and large tables are prohibited from using subqueries. Greatly affect database performance

[force] No negative queries and fuzzy queries starting with% are prohibited. A) negative query conditions: NOT,! =,!, NOT IN, NOT LIKE, etc., will result in full table scan. Fuzzy query at the beginning of b)% will result in full table scan.

[mandatory] the use of IN cannot exceed 200

[recommended] UNION ALL is used instead of UNION.

[suggestion] the order of order by should be consistent with the index as far as possible.

[forced] mass update operations in the early morning to avoid peak hours

After reading the above, do you have any further understanding of the specification of mysql database? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.