A php Method for mysql table sharding

One way to operate mysql table sharding in php is generally to consider Table sharding or partition when the data in our database exceeds records. This time I will introduce in detail some table sharding methods. First, we need to think about the number of tables to be divided, provided that the application is satisfied. Here I use a simple table sharding method, which is to divide the table based on the ending number of the auto-increment id. That is to say, there are 10 tables in total 0-9, and the value is also very good, it is the modulo of 10. In addition, you can use the md5 value of a field to retrieve several digits for table sharding. In this way, there will be a lot of tables that can be divided.
Now, create a table. 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_ciCREATE 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_ciCREATE 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_ciCREATE 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_ciCREATE 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_ciCREATE 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_ciCREATE 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_ciCREATE 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_ciCREATE 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_ciCREATE 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

After 10 tables are created, note that the IDs here cannot be set to auto-increment, and all table structures must be consistent, including the structure, type, and length, the order of fields must be consistent. How can I obtain this id? I will describe it in detail later. Now we need a merged table for query. The code for creating a merged 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: The merged table must have the same structure, type, length, and field sequence as the preceding table. Here, INSERT_METHOD = 0 indicates that the insert operation on the table is not allowed. Well, when you need to query, we can only operate on the article table. That is to say, this table can only perform the select Operation, so how should we do the insert operation? First, obtain the unique id. Here we need a table to 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

That is to say, when we need to insert data, this table must generate the id value. The method of my php code is as follows:

 db->query($sql);     return $this->db->insertID(); } ?>

Okay. Now, if we want to insert a piece of data, what should we do? Continue to read the code.

 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 );} /*** get the table name by id */function get_Table_Name ($ id) {return 'Article _'. intval ($ id) % 10 ;}?>

In fact, it is very simple, right, that is, to get the id first, and then obtain the table to which it should be inserted according to the id, then it is very easy.
I don't think we need to talk about the update operation. It's nothing more than having an id, getting the table name, and then performing the update operation.
For a user table, create a username with the minimum column of basic information, such as the user ID, user name, and password. Other user information is distributed to a table that is partitioned by user ID.
How to split tables depends on business needs.

You can score by id or by year, month, or region. According to business needs.

What if you need a total query? You can temporarily merge tables for query.