Shulou(Shulou.com)06/01 Report--

This paper mainly introduces the five-dimensional starting point handout of MySQL review table structure design, which involves things, learned from the theoretical knowledge, there are many books and documents for your reference, from the perspective of practical significance, accumulated years of practical experience can be shared with you.

1. Data scenarios

1. Brief introduction of table structure

The purpose of any tool is to solve the problems in a certain scenario, such as Redis caching hot data, ClickHouse solving real-time analysis of massive data, and MySQL relational database storing structured data. The storage of data needs to design the corresponding table structure, clear table structure, which is helpful to the rapid development of business and understanding of the system. The design of table structure is usually considered from the following aspects: business scenario, design specification, table structure, field properties, and data management.

2. User scenario

For example, to store user basic information data, there are usually the following related table structures: user information table, single sign-on table, status management table, payment account table and so on.

User information table

Store three elements of user-related information: name, mobile phone number, ID card, login password, mailbox and so on.

CREATE TABLE `int (11) NOT NULL AUTO_INCREMENT COMMENT 'user ID', `user_ name` varchar (20) NOT NULL COMMENT' username', `real_ name` varchar (20) DEFAULT NULL COMMENT 'real name', `pass_ word` varchar (32) NOT NULL COMMENT 'password', `phone`varchar (20) NOT NULL COMMENT 'mobile number', `email`varchar (32) DEFAULT NULL COMMENT 'mailbox', `head_ url` varchar 'user profile URL' `card_ id` varchar (32) DEFAULT NULL COMMENT'ID number', `user_ sex` int (1) DEFAULT'1' COMMENT 'user gender: 0-female, 1-male', `create_ time`datetime DEFAULT NULL COMMENT 'creation time', `update_ time`datetime DEFAULT NULL COMMENT 'update time', `state`int (1) DEFAULT'1' COMMENT'is available, 0-unavailable, 1-available', PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' user table' Single point login form

The intention is that in multiple business systems, users can access all the business subsystems that trust each other at one login, which is a common solution for the converged business platform.

CREATE TABLE `ms_user_ Sso` (`sso_ id` int (11) NOT NULL COMMENT 'user ID', `sso_ id` varchar (32) NOT NULL COMMENT' single point information number ID', `sso_ code `varchar (32) NOT NULL COMMENT 'single sign-on code, unique core ID', `log_ ip`varchar (32) DEFAULT NULL COMMENT 'login IP address', `create_ time`datetime DEFAULT NULL COMMENT 'creation time, `update_ time`datetime DEFAULT NULL COMMENT' update time' `state`int (1) DEFAULT'1' COMMENT'is available, 0-unavailable, 1-available', PRIMARY KEY (`stateid`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' user single sign-on form' Status management table

System users may have multiple states when using them, such as account freezing, password locking, etc., which can be more easily managed and verified by aggregating the states together.

CREATE TABLE `ms_user_ status` (`pay_pass_ id` int (11) NOT NULL COMMENT 'user ID', `account_ status` int (1) DEFAULT' 1' COMMENT 'account status: 0-frozen, 1-unfrozen', `real_name_ status` int (1) DEFAULT'0' COMMENT 'identity authentication status: 0-not real name, 1-real name', `pay_pass_ status` int (1) whether the payment password is set: 0-not set, 1-set' `email_ status` int (1) DEFAULT'0' COMMENT 'wallet password is set: 0-not set, 1-set', `wallet_ status` int (1) DEFAULT'1' COMMENT 'wallet is frozen: 0-frozen, 1-unfrozen', `email_ status` int (1) DEFAULT'0' COMMENT 'mailbox status: 0-inactive, 1-activate', `message_ status` int (1) DEFAULT'1' COMMENT 'SMS reminder: 0-not enabled 1-on', 'letter_ status` int (1) DEFAULT' 1' COMMENT 'internal message reminder: 0-not enabled, 1-enable', `emailmsg_ status`int (1) DEFAULT'0' COMMENT 'email reminder: 0-not enabled, 1-enable', `update_ time`datetime DEFAULT NULL COMMENT 'creation time', `state`timetime`update time', `state`int (1) DEFAULT'1' COMMENT 'whether available, 0-unavailable, 1-available' PRIMARY KEY (`user_ id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' user status table' Payment account form

The core table of the user transaction, which stores the user-related account fund information.

CREATE TABLE `ms_user_ wallet` (`wallet_ id` int (11) NOT NULL AUTO_INCREMENT COMMENT 'wallet ID', `user_ id` int (11) NOT NULL COMMENT' user ID', `wallet_ pwd` varchar (32) DEFAULT NULL COMMENT 'wallet password', `total_ wallet` decimal (20penny 2) DEFAULT '0.00' COMMENT' account total', `usable_ money`decimal (20d2) DEFAULT '0.00' COMMENT' available balance', `freeze_ money` decimal (20ji2) DEFAULT '0.00' COMMENT' frozen amount' `freeze_ time`datetime DEFAULT NULL COMMENT 'freeze time', `create_ time`datetime DEFAULT NULL COMMENT 'creation time', `update_ time`datetime DEFAULT NULL COMMENT 'update time', `state`int (1) DEFAULT'1' COMMENT 'whether available, 0-unavailable, 1-available', PRIMARY KEY (`timeid`) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' user wallet' Second, design specifications 1, involving modules

Through the above cases of table design, we can see that all aspects of table design are related to the database: data type, index, coding, storage engine, and so on. Table design is a big proposition, but it also follows a basic specification: three paradigms.

2. The basic concept of three paradigms.

One paradigm

The column of the table is atomic and can not be decomposed, that is, the information of the column can not be decomposed, and the relational database MySQL, Oracle and so on can be satisfied automatically.

Two paradigms

The data record of each fact will appear only once and will not be redundant, usually a primary key is designed to implement it.

Three paradigms

It is required that a table does not contain non-primary key information that already exists in other tables, such as department and employee information, and the employee table contains the primary key ID of the department table, then the relevant information can be obtained by association, and there is no need to save the relevant information in the employee table.

Comparison of advantages and disadvantages

Model design

Stylized structural design is usually updated quickly because of less redundant data, lighter table structure, and better written into memory. But the query involves correlation, the cost is very high, and the query performance is very wasted.

Anti-stylized design

All the data is in one table, and the associated query is avoided. The index is more effective, but the data is highly redundant.

Suggestion conclusion

The above two design methods do not exist in the actual development, and are mixed in the actual development. For example, summary statistics, caching data, will be based on anti-stylized design.

III. Field properties

The appropriate field type is important for high performance, and the basic principles are as follows: a simple type takes up less resources; if the data can be stored correctly, choose the smallest data type.

1. Data type Select Integer Type

TINYINT, SMALLINT, MEDIUMINT, INT and BIGINT can be selected reasonably according to the range of data types.

Real number type

FLOAT, DOUBLE, DECIMAL, it is recommended that capital and currency related types use high-precision DECIMAL storage, or multiply the data to integers, using BIGINT storage, but it is relatively troublesome to deal with.

Character type

CHAR, VARCHAR. VARCHAR storage is recommended for uncertain length, but VARCHAR type requires extra overhead to record string length. CHAR is suitable for storing short characters or fixed-length strings, such as the encryption structure of MD5.

Time type

DATETIME, TIMESTAMP,DATETIME save a wide range of values, precision seconds. TIMESTAMP is in the format of timestamps, which is relatively small in scope and relatively efficient, so it is generally recommended.

There are many field types for MySQL, and you can choose the right one according to the characteristics of the data. Only a few common types are described here.

2. Basic usage operation data type

Modify field type

ALTER TABLE ms_user_sso MODIFY state CHAR (1) DEFAULT'0'; ALTER TABLE ms_user_sso MODIFY state INT (1) DEFAULT'1' COMMENT 'status: 0 unavailable, 1 available'

Modify name location

ALTER TABLE ms_user_sso CHANGE log_ip login_ip VARCHAR (32) AFTER update_time; index usage

Index types: primary key index, general index, unique index, combined index, full-text index. The operation of a normal index is demonstrated here. The core module of MySQL, which will be described in detail later.

Add Index

ALTER TABLE ms_user_wallet ADD INDEX user_id_index (user_id); CREATE INDEX state_index ON ms_user_wallet (state)

View Index

SHOW INDEX FROM ms_user_wallet

Delete index

DROP INDEX state_index ON ms_user_wallet

Modify the index

There is no real modification, you can delete the original index and add the index again.

Foreign key association

Usefulness: the role of foreign key association ensures the data consistency and integrity of multiple data tables. When building a table, there is a master table, then a slave table; to delete a data table, you need to delete the slave table first, and then delete the master table. It is not recommended to use complex scenarios, and not much is used in actual development.

Add foreign key

ALTER TABLE ms_user_wallet ADD CONSTRAINT user_id_out_key FOREIGN KEY (user_id) REFERENCES ms_user_center (id)

Delete foreign key

ALTER TABLE ms_user_wallet DROP FOREIGN KEY user_id_out_key; 4. Table structure management 1. View structure DESC ms_user_status; SHOW CREATE TABLE ms_user_status; 2. Field structure add field ALTER TABLE ms_user_status ADD `delete_ time` datetime DEFAULT NULL COMMENT 'delete time'; delete field ALTER TABLE ms_user_status DROP COLUMN delete_time; 3. Modify table name ALTER TABLE ms_user_center RENAME ms_user_info 4. Storage engine SELECT VERSION (); SHOW ENGINES

MySQL 5.6supports storage engines such as InnoDB, MyISAM, Memory, Archive, CSV, BLACKHOLE, etc. InnoDB is generally used by default to support transaction management. The MySQL core of the module will be explained in detail later.

Modify engine

In the scenario of a large amount of data, the modification of the storage engine is a very difficult operation, which will easily lead to changes in the characteristics of the table, causing a variety of follow-up reactions, which will be described in detail later.

ALTER TABLE ms_user_sso ENGINE = MyISAM; 5. Modify the code

The table character set uses utf8 by default, universal, no garbled risk, 3 bytes of Chinese characters, 1 byte of English, utf8mb4 is a superset of utf8, and 4 bytes are stored, such as emoticons.

View the code SHOW VARIABLES LIKE 'character%';, modify the code ALTER TABLE ms_user_sso DEFAULT CHARACTER SET utf8mb4; 5, data management 1, add, delete, modify and check

Add data

INSERT INTO ms_user_sso (user_id,sso_id,sso_code,create_time,update_time,login_ip,state) VALUES (127.0.0.1) VALUES ("INSERT INTO ms_user_sso (SSO7637267)", "" SSO78631273612 "," 2019-12-24 11 "

Update data

UPDATE ms_user_sso SET user_id = '1century journal ssoothid =' SSO20191224',sso_code = 'SSO20191224', create_time =' 2019-11-24 11V 56V 57V R updatekeeper time = '2019-11-2411V 57V 01L, login_ip =' 127.0.0.1 minute state = '1'WHERE user_id =' 1'

Query data

In general, the use of select* operations is prohibited.

SELECT user_id,sso_id,sso_code,create_time,update_time,login_ip,state FROM ms_user_sso WHERE user_id ='1'

Delete data

DELETE FROM ms_user_sso WHERE user_id ='2'

Without where conditions, all data is deleted. This operation is not allowed in principle, and the optimized text will be explained in detail. TRUNCATE TABLE also empties the table data, but consumes relatively few resources.

2. Irreversible encryption of data security

This kind of encryption algorithm is often used to do data verification operations, such as common password verification.

SELECT MD5 ('cicada') =' 94454b1241ad2cfbd0c44efda1b6b6ba'; SELECT SHA ('cicada') =' 0501746a2e4fd34e1d14015fc4d58309585edc7dskeleton select PASSWORD ('smile') =' * B4FB95D86DCFC3F33A38527DC742C77504479D'

High security requirements of the system, need to do three-level protection, the security of the data is extremely high, the data must be encrypted into the storage, take out the need to decrypt, these need reversible encryption.

SELECT DECODE (ENCODE ('123456)),' key_salt'); SELECT AES_DECRYPT (AES_ENCRYPT ('cicada','salt123'),' salt123')

The above data security management can also be handled based on the service (code) layer of the application system, and the relatively professional process is to process from the source of data generation to avoid leakage in the process of data transmission and cause unnecessary risks.

Source code address GitHub address https://github.com/cicadasmile/mysql-data-baseGitEE address https://gitee.com/cicadasmile/mysql-data-base

Read the above MySQL Review Table structure design of the five dimensions of the starting point handout introduction, hoping to bring some help to everyone in the practical application. Due to the limited space in this article, there will inevitably be deficiencies and areas that need to be supplemented. You can continue to pay attention to the industry information section and will update your industry news and knowledge regularly. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.