engineering uni league tables
Mysql table sharding method ----- MRG_MyISAM engine table sharding method generally, when the data in our database exceeds records, we should consider table sharding or partition. this time I will detail some table sharding methods. Currently, all the methods I know are MYISAM. I do not know much about how INNODB performs table sharding and retains transactions and foreign keys.
First,
search engine land s
eo periodic table
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.
S
earch engine land periodic table
Now, create a table. the code is as follows:
CREATE TABLE `test`.`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 `test`.`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 `test`.`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 `test`.`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 `test`.`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 `test`.`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 `test`.`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 `test`.`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 `test`.`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 `test`.`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
Civil engineering tables
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 `test`.`article` ( `id` BIGINT( 20 ) NOT NULL , `subject` VARCHAR( 200 ) NOT NULL , `content` TEXT NOT NULL , PRIMARY KEY ( `id` ) ) ENGINE=CREATE TABLE user ( `id` int(11) NOT NULL PRIMARY key, `username` varchar(20) NOT NULL, `passwd` char(32) NOT NULL) ENGINE=MERGE DEFAULT CHARSET=utf8 INSERT_METHOD=no UNION=(article_0,article_1,article_2,article_3,article_4,article_5,article_6,article_7,article_8,article_9);
Here INSERT_METHOD = 0 may not work in some versions. you need to change it to INSERT_METHOD = NO.
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.
In this case, how should we perform the insert operation? First, we need to obtain the unique id. here we need a table to create the id. the code is as follows:
engineering d
r
awing table buy online
CREATE TABLE `test`.`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:automotive engineering league tables
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.
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.
