MySQL database design preliminary specification V1.0

Database design specifications:

1, table design specifications

1.1 About table design

a)    Table Names and column names must be commented.

b) The name should use meaningful English words or abbreviations, consisting of multiple words, all in capital letters, separated by "_", and only English letters and numbers can be used. and underscores, no spaces. For example, USER_DETALL does not allow the use of keywords TYPE or STATUS as field names.

c) The naming length should not exceed 15 characters (avoid exceeding 20). It should reflect the business scope of the data set, or business functions, such as POWER_USER (User Center), etc.

d) When the field type is an enumeration or Boolean, use the CHAR(1) (or CHAR(2)) type to fill in the default value. The default value of the status field cannot be Null is generally set to 0 or -1, and the description of the status field is written as comment 'Group buying coupon status: 1. Purchased; 2. Used; 3. Refunding; 4 Refunded'.

e) Try to include date fields when designing: CREATE_DATE (creation date), UPDATE_DATE (update date), etc. Mysql agrees on an entry method for dates, such as '2014-12-31 00:00:00.0'

f) The default value is 0 for numeric types and 0 for strings The value is '' and the default value for date is '1900-01-01 00:00:00.0'.

g) Use bigint for the primary key field ID. If there is an AUTO_INCREMENT=6653864 mark in the create statement, please remove it.

h) The default value of the date field cannot be null, and is generally set to 1970-12-31 00:00:00.0.

i) The mobile phone field, email field and other fields that will be retrieved are not allowed to be null, and the default value is the empty string ''. Numeric type fields are not allowed to be null and the default value is 0.

j) The default character encoding is utf8, and the default storage engine is INNODB

PS: Each table must have a primary key field and must be entered. Date fields and values ​​are not allowed to be NULL.

1.2 Index design

1)                                                                                                                                                    

#2)                                                                                                                                                                                                                       Establishing an index field for a field with a small repetition rate in the value range, such as the CREATE_DATE (entry time) field; fields with a repetition rate in the value range do not need to be indexed, such as IS_RETURN (whether it has been returned). paragraph) field.

## 3) The main key field does not need to build Unique Key, and the main key field does not need to build indexes separately.

4) The frequently queried fields behind the WHERE condition need to be indexed, such as the ORDER_SN (product number) of the ORDER_GOODS table, etc.

5) Range fields do not need to be indexed, such as the IS_DEL field of the SHOP_MALL table, etc.

6) The fields to be indexed must not have null values, otherwise it will affect the efficiency of the index.

1.3 Table structure example

Table statement example ：

##CREATE TABLE `SHOP_GAY` (

`ID` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT 'Shop ID',

## `SHOP_NAME` VARCHAR(50) DEFAULT '' COMMENT 'Store name',

`LEGAL_PERSON_MOBILE` VARCHAR(11) DEFAULT NULL COMMENT 'Legal mobile phone',

`SCORE` BIGINT(20) DEFAULT 0 COMMENT 'Points',

... ...

`MANAGER_NAME` VARCHAR(20) DEFAULT '' COMMENT 'Store manager name',

`BRIEF` VARCHAR(500 ) DEFAULT '' COMMENT 'Store profile',

`HAS_WAREHOUSE` CHAR(1) DEFAULT '0' COMMENT 'Whether there is a warehouse, 0: No; 1: Yes ',

`DESCRIPTION_FIT` DECIMAL(3,1) DEFAULT 0 COMMENT 'The description matches--it is obtained by calculating the average value of all order items and taking one decimal place',

`BACKGROUND` VARCHAR(200) DEFAULT '' COMMENT 'Store title picture',

`CREATED_DATE` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',

`UPDATED_DATE` DATETIME DEFAULT '1970-12-31 00:00:00.0' COMMENT 'Update time',

PRIMARY KEY (`ID`),

KEY IDX_MOB(LEGAL_PERSON_MOBILE),

KEY IDX_CRETIME(CREATED_DATE),

KEY IDX_UPTIME(UPDATED_DATE )

) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='GAY STORE'

##Add field example:

ALTER TABLE AUTH_MALL ADD COLUMN SHORT_NAME VARCHAR(20) DEFAULT '' COMMENT 'Square name abbreviation' AFTER FULL_NAME ;

Example of modifying table fields:

ALTER TABLE GATEWAY_PAYMENT_ORDER MODIFY COLUMN STAT varchar(2) DEFAULT '0'

comment 'Transaction status 0: pending payment/refund, 1: waiting for callback from third-party channels, 2: payment/refund successful, 3: payment/refund failed, 4: payment/refund confirmation successful , 5: Payment/refund confirmation failed, 6: Transaction closed, 7: Pending payment (if this is the status - you need to confirm whether the receiving account is normal), 8: Payment/refund confirmation successful - no other operations are allowed, 9: Verification failed, 10: Synchronous confirmation/Buyer has paid - waiting for seller to ship WAIT_SELLER_SEND_GOODS, 11: Synchronous confirmation/Seller has shipped, waiting for buyer to confirm WAIT_BUYER_CONFIRM_GOODS' AFTER DESCRIPTION;

##2, SQL writing

2.1, try to use single table query, Avoid multi-table JOIN. The subsequent ON conditions of JOIN cannot be judged by OR, such as SELECT A.C1,B.C2 FROM A,B ON(A.ID=B.PID OR B.TAG=A.TAR_GET); OR performance is very low, PS: We Some function modules online that open slowly are caused by this OR writing method.

2.2. Write the SQL statement to the application and prohibit all DDL operations, such as: create, drop, alter, grant, remove ; If you have special needs, please consult with the DBA before using it.

2.3. When writing SQL, be sure to specify the table name as a prefix for each field. For example, select ub.id,ub.name from user_business ub where ub.create_date > ''; In the iBatis SQLMap file, the binding variable is represented by "#var_name", and the substitution variable is represented by "$var_name$"; all require dynamic order When using substitution variables for queries with by conditions, the possible incoming content needs to be hard-coded in the code as enumerations, and it is prohibited to accept external incoming content.

2.4. If you need transaction support, when using innodb, turn off automatic submission first when connecting to the database. For example: set auto_commit=0; When writing Java code, in the case of transaction processing, after executing insert, delete, and update, commit; in the exception code block, the rollback operation must be written.

2.5, do not write code similar to select *, the field names need to be specified.

2.6, MySQL’s dates and characters are the same, so there is no need to do other conversions like Oracle, such as:

select e.username from employee e where e.birthday>='1998-12-31 11:30:45'.

2.7, avoid applying functions to fields in where clauses, except if it is a business requirement, but you need to consult the DBA when writing . For example, DATE_FORMAT(p.PAYMENT_DATE, '%Y-%m-%d') >= DATE_FORMAT('2014-10-01', '%Y-%m-%d') needs to be corrected.

2.8. Avoid redundant sorting. When using group by, sorting will be performed by default. When you do not need to sort, You can use order by null;

2.9. When tables are connected, if the data types of the fields in the two tables used for connection are inconsistent , you must add a type conversion function on one side. Prevent mysql from doing implicit type conversion.

2.10. It is forbidden to perform batch update sql operations on the database in the application. If necessary, please send an email and the DBA will judge the appropriateness. Within the time period, it is manually executed on the IDC library.

3. Basic principles

PS: Table structure changes must be initiated by the team to which the database table belongs.

1. All SQL statements that change the table structure executed in the test environment must be reviewed by the DBA.

2, physical deletion is not allowed, stored procedures, triggers and views are not allowed, special circumstances and business scenarios apply for DBA

PS: All specifications are adapted to your own business scenarios. Everyone is welcome to make better suggestions. I will also continue to summarize and expand the most suitable database specifications according to the development of the business.

The above is the content of MySQL database design preliminary specification V1.0, more related content Please pay attention to the PHP Chinese website (www.php.cn)!

#