How PHP operates mysql database table splitting, phpmysql database

Generally speaking, when the data in our database exceeds 1 million records, we should consider splitting tables or partitions , this time I will talk about some methods of dividing tables in detail. First of all, we need to think about how many tables to divide, and the premise is of course that the application is satisfied. Here I used a relatively simple table division method, which is to divide the tables according to the mantissa of the auto-incrementing ID. That is to say, there are 10 tables divided into 0-9. The value is also easy to do, which is to take the modulo 10. In addition, you can also select several of them to divide the tables according to the md5 value of a certain field. In this case, there will be many tables that can be divided.

Okay, let’s create the table first. The code is as follows:

CREATE TABLE `ttlsa_com`.`article_0` ( 
`id` BIGINT( 20 ) NOT NULL ,
`subject` VARCHAR( 200 ) NOT NULL ,
`content` TEXT NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci
CREATE TABLE `ttlsa_com`.`article_1` ( 
`id` BIGINT( 20 ) NOT NULL ,
`subject` VARCHAR( 200 ) NOT NULL ,
`content` TEXT NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci
CREATE TABLE `ttlsa_com`.`article_2` ( 
`id` BIGINT( 20 ) NOT NULL ,
`subject` VARCHAR( 200 ) NOT NULL ,
`content` TEXT NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci
CREATE TABLE `ttlsa_com`.`article_3` ( 
`id` BIGINT( 20 ) NOT NULL ,
`subject` VARCHAR( 200 ) NOT NULL ,
`content` TEXT NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci
CREATE TABLE `ttlsa_com`.`article_4` ( 
`id` BIGINT( 20 ) NOT NULL ,
`subject` VARCHAR( 200 ) NOT NULL ,
`content` TEXT 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( 200 ) NOT NULL ,
`content` TEXT NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci
CREATE TABLE `ttlsa_com`.`article_6` ( 
`id` BIGINT( 20 ) NOT NULL ,
`subject` VARCHAR( 200 ) NOT NULL ,
`content` TEXT NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci
CREATE TABLE `ttlsa_com`.`article_7` ( 
`id` BIGINT( 20 ) NOT NULL ,
`subject` VARCHAR( 200 ) NOT NULL ,
`content` TEXT NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci
CREATE TABLE `ttlsa_com`.`article_8` ( 
`id` BIGINT( 20 ) NOT NULL ,
`subject` VARCHAR( 200 ) NOT NULL ,
`content` TEXT 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( 200 ) NOT NULL ,
`content` TEXT NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci

Okay, 10 tables have been created. It should be noted that the id here cannot be set to auto-increment, and all table structures must be consistent, including structure, type, length, and the order of fields must be consistent. So for this How to get the id? I will elaborate on this later. Now, we need a merge table for query. The code to create the merge table is as follows:

CREATE TABLE `ttlsa_com`.`article` ( 
`id` BIGINT( 20 ) NOT NULL ,
`subject` VARCHAR( 200 ) 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 field order as the previous table. INSERT_METHOD=0 here means that insert operations are not allowed on this table. Okay, when we need to query, we can only operate on the article table. That is to say, this table can only perform select operations. So how to perform insert operations? The first thing is to get A unique id. A table is needed to specifically create the id. The code is as follows:

CREATE TABLE `ttlsa_com`.`create_id` ( 
`id` BIGINT( 20 ) NOT NULL AUTO_INCREMENT PRIMARY KEY 
) ENGINE = MYISAM

In other words, when we need to insert data, the id value must be generated by this table. The method of my php code is as follows:

<&#63;php 
function get_AI_ID() { 
$sql = "insert into create_id (id) values('')"; 
$this->db->query($sql); 
return $this->db->insertID(); 
} 
&#63;>

Okay, now assuming we want to insert a piece of data, how should we do it? Let’s continue looking at the code

<&#63;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}','測試標題','測試內(nèi)容')"; 
$this->db->query($sql); 
} 
/** 
* 用于根據(jù)id獲取表名 
*/ 
function get_Table_Name($id) { 
return 'article_'.intval($id)%10; 
} 
&#63;>

It’s actually very simple, right? Just get the id first, and then get which table it should be inserted into based on the id. Then it’s very simple.
I think there is no need to say more about the update operation. It is nothing more than having the id, then getting the table name, and then performing the update operation.
For the user table, create a user name with at least the most basic information, such as user ID, user name, and password. Other user information is distributed to tables divided into tables based on user IDs.
How to divide tables depends on business needs.

You can sort by ID, or by year, month, or region. According to business needs.

The above is the method that the editor introduces to you on how to use PHP to operate mysql database sub-tables. I hope it will be helpful to everyone. If you have different opinions, please feel free to put them forward and learn and make progress together!