This article example describes the optimization of data tables in Mysql databases, foreign keys and three-paradigm usage. It is shared for your reference as follows:
Data Table Optimization
Optimize the product information table
1. Create a table of product categories:
create table if not exists goods_cates( id int unsigned primary key auto_increment, name varchar(40) not null );
2. Write the product type in the product type table:
Note: You don't need to add values to the results of a query inserted into another table.
insert into goods_cates(name) (select cate_name from goods group by cate_name);
3. Update the product type table to the product table:
Joins the product table to the category table, replacing the content of the product table category with the id of the category table
update (goods_cates inner join goods on goods_cates.name=goods.cate_name) set goods.cate_name=goods_cates.id;
4. Modify the table structure:
Rename the product table product category name field name cate_name to cate_id
alter table goods change cate_name cate_id int unsigned not null;
foreign key
1. The use of foreign keys:
Add a foreign key constraint, insert the data line to determine whether the cate_id exists in goods_cates, if not, the insertion of data fails
alter table table name1 add foreign key (field name1) references table name2(field name2);
(Setting a foreign key prevents illegal data entry)
foreign key(field name1) references table name2(field name2);
(You can also set the foreign key directly when creating the table, provided that the table to be joined exists)
2. Disadvantages of foreign keys:
Although foreign keys can effectively prevent the entry of illegal data, but will greatly reduce the efficiency of the table update, so the actual development, generally do not apply the foreign key constraints, you can then go to the transaction level to determine the legitimacy of the data.
3. Deletion of foreign keys:
Display the statement that creates the table
show create table goods;
Delete a foreign key after getting its name:
alter table goods drop foreign key goods_ibfk_1;
triple paradigm (math.)
1. First Paradigm (1NF): Emphasizes the atomicity of columns, i.e., columns cannot be subdivided into other columns.
2. Second Paradigm (2NF): Based on 1NF, the table must have a primary key, and the non-primary key fields must be fully dependent on the primary key, not just partially dependent on the primary key.
3. Third Paradigm (3NF): Based on 2NF, non-primary key columns must be directly dependent on the primary key, there can be no passing dependencies.
A database that conforms to the third paradigm is a more reasonable database.
Readers interested in more MySQL-related content can check out this site's topic:MySQL Query Tips》、《MySQL Functions》、《MySQL Logging Tips and Tricks》、《MySQL Transaction Operations Tips Summary》、《MySQL Stored Procedure Tips and Tricksand theMySQL Database Locking Related Tips Summary》
I hope that the description of this article will help you MySQL database meter.