PHP Operation MySQL database table method _php instance

In general, when our database data exceeds 100w records, we should consider the table or partition, this time I would like to elaborate on some of the methods of the table. First of all, we need to think about how many tables in the end, the premise of course is to meet the application. Here I use a relatively simple method of the table, which is based on the mantissa of the ID, which is divided into 0-9 altogether 10 tables, the value is also very good to do, is to 10 to take the mold. In addition, can also be based on the MD5 value of a field to take several of them to the table, so that the table can be divided into a lot of.

OK, let's start by creating a table with the following code:

CREATE TABLE ' ttlsa_com '. ' Article_0 ' (' id ' BIGINT ' () not NULL, ' subject ' VARCHAR ' is not null, ' content ' TEXT N OT NULL, PRIMARY KEY (' id ')) ENGINE = MYISAM CHARACTER SET UTF8 COLLATE CREATE TABLE ' utf8_general_ci '. ' Ttlsa_com
Cle_1 ' (' id ' BIGINT ' NOT null, ' subject ' VARCHAR ' is not null, ' content ' TEXT is not null, PRIMARY KEY (' id ')  ENGINE = MYISAM CHARACTER SET UTF8 COLLATE utf8_general_ci CREATE TABLE ' ttlsa_com '. ' Article_2 ' (' ID ' BIGINT () not  NULL, ' subject ' VARCHAR not NULL, ' content ' TEXT is not null, PRIMARY KEY (' id ')) ENGINE = MYISAM CHARACTER SET UTF8 COLLATE utf8_general_ci CREATE TABLE ' ttlsa_com '. ' Article_3 ' (' id ' BIGINT ' not NULL, ' subject ' VARCHAR (200 Not NULL, ' content ' TEXT is not null, PRIMARY KEY (' id ')) ENGINE = MYISAM CHARACTER SET UTF8 COLLATE utf8_general_ci C  reate TABLE ' ttlsa_com '. ' Article_4 ' (' id ' BIGINT ' NOT null, ' subject ' VARCHAR ') is not null, ' content ' TEXT is not NULL, PRIMARY KEY (' Id ')) ENGINE = MYISAM CHARACTER SET UTF8 COLLATE utf8_general_ci CREATE TABLE ' ttlsa_com '. ' Article_5 ' (' ID ' BIGINT (20 Not NULL, ' subject ' VARCHAR is not null, ' content ' TEXT is not null, PRIMARY KEY (' id ')) ENGINE = MYISAM characte  R SET UTF8 COLLATE utf8_general_ci CREATE TABLE ' ttlsa_com '. ' Article_6 ' (' id ' BIGINT ' not NULL, ' Subject ' VARCHAR ( Not NULL, ' content ' TEXT is not null, PRIMARY KEY (' id ')) ENGINE = MYISAM CHARACTER SET UTF8 COLLATE _ci CREATE TABLE ' ttlsa_com '. ' Article_7 ' (' id ' BIGINT not NULL, ' subject ' VARCHAR ' is not null, ' content ' TEX T not NULL, PRIMARY KEY (' id ') ENGINE = MYISAM CHARACTER SET UTF8 COLLATE CREATE TABLE ' utf8_general_ci '. '  Rticle_8 ' (' id ' BIGINT ' NOT null, ' subject ' VARCHAR ' is not null, ' content ' TEXT is not null, PRIMARY KEY (' id ' ) ENGINE = MYISAM CHARACTER SET UTF8 COLLATE utf8_general_ci CREATE TABLE ' ttlsa_com '. ' Article_9 ' (' ID ' BIGINT (20) Not NULL, ' subject ' VARCHAR () Not NULL, ' content ' TEXT is not null, PRIMARY KEY (' id ')) ENGINE = MYISAM CHARACTER SET UTF8 COLLATE Neral_ci

All right, 10 tables have been created, it should be noted that the ID here cannot be set to the self increase, and all table structures must be consistent, including structure, type, length, and the order of the fields must be consistent so how do you get this ID? I'll explain in detail later. Now we need a merge table for the query, and the code to create the merged table is as follows:

CREATE TABLE ' ttlsa_com '. ' article ' ( 
' id ' BIGINT ' NOT null,
' subject ' VARCHAR ' is not null,
' content ' TEXT not NULL,
PRIMARY KEY (' id ')
engine=mrg_myisam DEFAULT Charset=utf8 insert_method=0 union = (' Article_0 ', ' article_1 ', ' article_2 ', ' article_3 ', ' article_4 ', ' article_5 ', ' article_6 ', ' article_7 ', ' article_8 ', ' article_9 ' ;

Note that the merged table must also have the same structure, type, length, and the order of the fields as in the previous table. The insert_method=0 here indicates that the insert operation is not allowed on this table. Well, when we need to query, we can just do the article this table, which means that the table can only select operations, so for inserts that insert operation should be done, the first is to get a unique ID, Here you will also need a table to create the ID specifically, the following code:

CREATE TABLE ' ttlsa_com '. ' create_id ' ( 
' id ' BIGINT () not NULL auto_increment PRIMARY KEY 
) ENGINE = MYISAM

That is, when we need to insert data, we have to generate ID values from this table, and my PHP code has the following method:

<?php 
function get_ai_id () { 
$sql = "INSERT into create_id (ID) VALUES (')"; 
$this->db->query ($sql); 
return $this->db->insertid (); 
>

OK, now suppose we're going to insert a piece of data, what should we do? Let's just keep looking at the code.

<?php 
function new_article () { 
$id = $this->get_ai_id (); 
$table _name = $this->get_table_name ($id); 
$sql = "INSERT INTO {$table _name} (id,subject,content) VALUES (' {$id} ', ' Test title ', ' Test content ')"; 
$this->db->query ($sql); 
} 
/** 
* is used to get the table 
name 
/function Get_table_name ($id) {return 
' Article_ '. Intval ($id)%10 
by ID. >

It's really simple, right, to get the ID and then get the table to insert according to the ID, and then it's easy.
For the operation of the update I think there should be no more, it is simply with the ID, and then get the table name, and then update operation is good.
For the user table, build a minimum list of the most basic information of the user name, such as user ID, user name, password. Other information about the user is distributed to the table with the user ID.
How the table is divided by the business requirements.

You can divide according to the ID, or according to the year, month, region. According to business requirements.

The above is a small set up to introduce the PHP operation of the MySQL database table method, I hope to help you, if you have different opinions welcome to propose, common learning progress!