How to operate mysql database sharding using PHP, phpmysql database _ PHP Tutorial

The method for PHP to operate mysql database table Sharding is phpmysql database. The method of using PHP to operate mysql database sharding. in phpmysql database, when the data in our database exceeds records, we should consider sharding or partitioning, this time I come to PHP to operate mysql database table sharding method, phpmysql database

Generally, when the data in our database exceeds million records, we should consider table sharding or partition. this time I will discuss in detail some methods of table sharding. 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:

<?php function get_AI_ID() { $sql = "insert into create_id (id) values('')"; $this->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.

<? 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 obtain the table name based on the 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.

The above is a small Editor to introduce you to the PHP method for operating mysql database table sharding. I hope it will be helpful to you. if you have different opinions, please join us to learn and make progress!

In general, when the data in our database exceeds records, we should consider sharding tables or partitions. this time I will come...