A method for PHP to operate MySQL sub-table

In general, when the data of our database exceeds the 100w record, 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 figure out how many tables, the premise of course is to meet the application. Here I used a relatively simple table method, that is, according to the mantissa of the self-increment ID points, that is, 0-9 total 10 tables, the value is also very good to do, that is, 10 to take the mold. In addition, can be based on the MD5 value of a field to take a few of them to the table, so that can be divided into a lot of tables.
OK, let's create the table first, the code is as follows:

CREATE TABLE ' ttlsa_com '. ' Article_0 ' (' id ' BIGINT ( -) not NULL, ' subject ' VARCHAR ( $Not null, ' content ' TEXT is not null, PRIMARY KEY (' id ')) ENGINE=MYISAM CHARACTER SET UTF8 COLLATE utf8_general_cicreate TABLE ' ttlsa_com '. ' Article_1 ' (' id ' BIGINT ( -) not NULL, ' subject ' VARCHAR ( $Not null, ' content ' TEXT is not null, PRIMARY KEY (' id ')) ENGINE=MYISAM CHARACTER SET UTF8 COLLATE utf8_general_cicreate 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_cicreate TABLE ' ttlsa_com '. ' Article_3 ' (' id ' BIGINT ( -) not NULL, ' subject ' VARCHAR ( $Not null, ' content ' TEXT is not null, PRIMARY KEY (' id ')) ENGINE=MYISAM CHARACTER SET UTF8 COLLATE utf8_general_cicreate TABLE ' ttlsa_com '. ' Article_4 ' (' id ' BIGINT ( -) not NULL, ' subject ' VARCHAR ( $Not null, ' content ' TEXT is not null, PRIMARY KEY (' id ')) ENGINE=MYISAM CHARACTER SET UTF8 COLLATE utf8_general_cicreate TABLE ' ttlsa_com '. ' Article_5 ' (' id ' BIGINT ( -) not NULL, ' subject ' VARCHAR ( $Not null, ' content ' TEXT is not null, PRIMARY KEY (' id ')) ENGINE=MYISAM CHARACTER SET UTF8 COLLATE utf8_general_cicreate 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 utf8_general_cicreate TABLE ' ttlsa_com '. ' Article_7 ' (' id ' BIGINT ( -) not NULL, ' subject ' VARCHAR ( $Not null, ' content ' TEXT is not null, PRIMARY KEY (' id ')) ENGINE=MYISAM CHARACTER SET UTF8 COLLATE utf8_general_cicreate TABLE ' ttlsa_com '. ' Article_8 ' (' id ' BIGINT ( -) not NULL, ' subject ' VARCHAR ( $Not null, ' content ' TEXT is not null, PRIMARY KEY (' id ')) ENGINE=MYISAM CHARACTER SET UTF8 COLLATE utf8_general_cicreate TABLE ' ttlsa_com '. ' Article_9 ' (' 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

Okay, all right. 10 tables created, it should be noted that the ID can not be set to self-increment, and all the table structure must be consistent, including structure, type, length, the order of the fields must be consistent then how to get this ID? I'll explain in detail later. Now we need a consolidated table for the query, and the code to create the merged table is as follows:

no 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 as the previous table, the type, the length, including the order of the fields must be consistent here the insert_method=0 indicates that insert operations on this table are not allowed. Well, when we need to query, we can only operate on the table article, that is, the table can only be a select operation, then for the insertion is the insert operation should be how to do, the first is to obtain a unique ID, Here you will also need a table to create the ID specifically, the code is as follows:

= MYISAM

That is, when we need to insert data, it must be generated by this table ID value, my PHP code method is as follows:

<? php function get_ai_id () {     "insert INTO create_id (ID) VALUES (')";     $this->db->query ($sql);      return $this->db->?>

OK, now suppose we want to insert a piece of data, what should we do? 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);} /** * To get the table name by ID*/function Get_table_name ($id) {return 'Article_'. intval ($id)%Ten; } ?>

In fact, it's very simple, right, is to get the ID, and then according to the ID to get into which table, then it is very simple.
For update operation I guess I don't need to say it anymore, just have the ID, then get the table name, and then do the update operation.
For user tables, create a user name that has the least basic information, such as user ID, user name, and password. Other information about the user is distributed to tables that are divided into tables with user IDs.
How to divide the table with the business requirements.

You can score according to the ID, or according to the year, month, region. To follow business needs.

What if you really need a total query? You can check it out in a temporary form.

A method for PHP to operate MySQL sub-table