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

This article is to share with you about the MySQL table specification, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

I. MySQL Table Establishment Specification 1.1, admission Specification level

Must: in order to enforce the requirements, must comply with, if there is a special need to review

It is strongly recommended that you abide by it as much as possible. If you are unable to comply, please give a remark.

Suggestion: for best practices, developers can adjust according to the actual situation.

1.2. Core rules of design class

Table character set selection UTF8 [must]

Production repository storage engine uses INNODB [must]

Do not store pictures, files in the database, do not use large text types [must]

Data consistency is guaranteed by the program without using foreign keys [must]

Prohibit the use of stored procedures, triggers [must]

The amount of data per table should be controlled below 2000W. If it is expected to exceed, split or archive migration plan should be made in advance [highly recommended]

1.3. Naming convention

Library name, table name, field name, index name using lowercase letters, underscore _ division [required]

Library name, table name, field name, index name do not use reserved words (keywords) [required]

The library name, table name and field name do not exceed 15 characters. Please see the meaning of the name [suggestion]

Index naming: [strongly recommended]

Unique index: "ux_ table name _ field name first 3 letters [_ field name first 3 letters]"

Non-unique index: "ix_ table name _ field name first 3 letters [_ field name first 3 letters]"

1.4. Field class specification

All fields are defined as NOT NULL [required]

Strings unify single quotation mark references instead of double quotation marks [must]

Use UNSIGNED to store non-negative integers [required]

Use DECIMAL to store exact floating point numbers [required]

Use TINYINT instead of ENUM type [must]

Split TEXT or BLOB type fields, or create tables independently [required]

Use UNSIGNED INT to store ipv4 addresses instead of CHAR (15) [highly recommended]

Non-Chinese fields use varchar to store variable-length strings [highly recommended]

Convert characters to digital storage as much as possible [recommended]

Use TIMESTAMP to store operation time * [recommended]

1.5. Index class specification

The number of indexes in a single table does not exceed 5 and the number of fields in a single index does not exceed 5 [strongly recommended]

Avoid redundant indexes [highly recommended]

Important SQL must be indexed [strongly recommended]

WHERE conditional columns of UPDATE and DELETE statements

Fields of ORDER BY, GROUP BY, DISTINCT

Fields of multi-table JOIN

The choice of primary key should be careful [strongly recommended]

First use a unique key that is not empty, and then choose a self-incrementing column or a sender

Do not use frequently updated columns, try not to select string columns, do not use UUID MD5 HASH,

Try not to index as much as possible without indexing [strongly recommended]

Do not index the null column

Do not index on low cardinality columns, such as "gender"

Second, build the table SQL audit platform 2.1.description

The purpose of this system is to learn and reduce the work of DBA audit and table building SQL. Achieve automatic audit basic rule issues

Advantages:

Reduces the work of DBA manual audit list

Platform operation, convenient

Automatic matching and auditing specifications through python

Disadvantages:

, only build tables for SQL audit

, relatively simple, later can use inception to audit and build tables, SQL+ online SQL+ backup, etc., please follow the blog http://blog.itpub.net/28823725/ later research and application

Audit description:

Using python program to analyze

Divided into: error,suggest error category

Display by field error

Other instructions:

This system was written around June 2015. In order to learn from inception and python, share this article only for learning and communication.

In addition, Inception has not been open source for long; please spray it gently when comparing with inception.

Related articles about Inception will be published later.

2.2. Platform flow chart

2.3. Build

Environment building

Django environment building

Ignore, please refer to other articles on my blog, in which there are detailed deployment commands

Python2.7 environment building

ignore

Platform building

Cd / chunlei/django

Create a project:

Django-admin.py startproject createsql_check

A directory createsql_check will be generated

Cd / chunlei/django/createsql_check

Ls

Createsql_check

Manage.py

Setting up Profil

Cd / chunlei/django/createsql_check/createsql_check

Vi settings.py

ALLOWED_HOSTS = ['IP address that can be accessed, or write * that is unrestricted']

INSTALLED_APPS = [

'django.contrib.admin'

'django.contrib.auth'

'django.contrib.contenttypes'

'django.contrib.sessions'

'django.contrib.messages'

'django.contrib.staticfiles'

'createsql_check'

]

EMPLATES = [

{

'BACKEND': 'django.template.backends.django.DjangoTemplates'

'DIRS': ['/ chunlei/chunlei/django/createsql_check/createsql_check/templates']

'APP_DIRS': True

'OPTIONS': {

'context_processors': [

'django.template.context_processors.debug'

'django.template.context_processors.request'

'django.contrib.auth.context_processors.auth'

'django.contrib.messages.context_processors.messages'

]

}

}

]

TIME_ZONE = 'Asia/Shanghai'

, set the url file of django

From django.conf.urls import include, url

From createsql_check.views import createcheck_create_table_check

From createsql_check.views import createcheck_create_sql

From createsql_check.views import createcheck_get_dba_error_suggest

From createsql_check.views import createcheck_to_mail

From createsql_check.views import to_input_confirm_info

From createsql_check.views import to_confirm

Urlpatterns = [

Url (r'^ createcheck _ create_table_check/$',createcheck_create_table_check)

Url (r'^ createcheck _ create_sql/$',createcheck_create_sql)

Url (r'^ createcheck _ get_dba_error_suggest/$',createcheck_get_dba_error_suggest)

Url (r'^ createcheck _ to_mail/$',createcheck_to_mail)

Url (r'^ createcheck _ to_input_confirm_info/$',to_input_confirm_info)

Url (r'^ createcheck _ to_confirm/$',to_confirm)

]

Create a directory

Cd / chunlei/django/createsql_check

Mkdir check_html

Mkdir log

Cd / chunlei/django/createsql_check/createsql_check

Configure 163 mailboxes:

Note: the company's mail service was used before. When this system is built with Baidu Cloud, 163 mailbox service is configured.

Configure the authorization code to the mail service

open

Cd / chunlei/django/createsql_check

Python manage.py runserver IP:port

2.4, interface and operation

RD audit table creation SQL interface

Test and build table SQL

Create table table_test (id int,name1 varchar (50), primary key (id))

Automatically analyze the problem

Fill in the relevant information of the form

RD confirms the ok, fill in the relevant information in the form, and submit it to DBA

Overview of submission information

Overview of RD submission information. Send DBA by default, or fill in the recipient of CC mail.

Send to DBA

DBA receives mail

DBA confirmation

Click "DBA confirmation" in the email

Fill in the database form information, instructions, etc., click yes / reject

Submit the result

These are the specifications of MySQL table building, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.