Shulou(Shulou.com)06/01 Report--

Error reporting: when Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist encounters Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist errors, you need to start from the following aspects: 1. Check to see if there are some tables that are not MYISAM engine tables, because MERGE engine is only applicable to MYISAM Table 2. Check whether there are tables that do not exist in union tables. 3. Check whether the MERGE refers to a table that is not in the same library, and the table does not specify a database name. 4. Compare whether the structure of each table (index, engine, column, character set, etc.) is consistent. MRG_MyISAM engine: CREATE TABLE `pvlogs_merge_ test` (`id` varchar (100) NOT NULL, `member_ id` int (11) DEFAULT NULL, `jsession` int (11) DEFAULT NULL, `ip`bigint (20) DEFAULT NULL, `search_ id` bigint (20) DEFAULT NULL, `info_ id` bigint (20) DEFAULT NULL, `lastmodify` timestamp NULL DEFAULT NULL, `discus` int (11) NOT NULL, `status` int (11) NOT NULL DEFAULT'0' COMMENT 'When the page (html) is open, this attribute will set 1 is open, PRIMARY KEY (`id`), KEY `info_ id` (`info_ id`) KEY `member_ id`, KEY `ip` (`ip`) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 UNION= (`pvlogs_ 20170313`, `pvlogs_ 20170312`, `pvlogs_ 20170311`, `pvlogs_ 20170310`, `pvlogs_ 20170309`, `pvlogs_ 20170308`, `pvlogs_ 20170307`, `pvlogs_ 20170306`, `pvlogs_ 20170305`, `pvlogs_ 20170304`, `pvlogs_ 20170303`, `pvlogs_ 20170302`, `pvlogs_ 20170301`, `pvlogs_ 20170228`, `pvlogs_ 20170227`, `pvlogs_ 20170226`, `pvlogs_ 20170225`, `pvlogs_ 20170224`, `pvlogs_ 20170223`, `pvlogs_ 20170222`, `pvlogs_ 20170221`, 201pvlogs_ 20170220`, pvlogs_ 20170192`, pvlogs_ 20170218pvlogs_, 201702` `pvlogs_ 20170214`, `pvlogs_ 20170213`, `pvlogs_ 20170212`, `pvlogs_ 20170211`, `pvlogs_ 20170210`, `pvlogs_ 20170209`, `pvlogs_ 20170208`, `pvlogs_ 20170207`, `pvlogs_ 20170206`, `pvlogs_ 20170205`, `pvlogs_ 20170204`, `pvlogs_ 20170203`, `pvlogs_ 20170202`, `pvlogs_ 201702012`, `pvlogs_ 20170131`, `pvlogs_ 20170130`, `pvlogs_ 20170129`, `pvlogs_ 20170128`, `pvlogs_ 20170127`, `pvlogs_ 20170126`, `pvlogs_ 20170125`, `pvlogs_ 20170124`, pvlogs_ 20170123`, `pvlogs_ 20170122`, pvlogs_ 201701``, pvlogs_ 1201`, pvlogs_ `201701`, pvlogs_ 201701`, 201701` `pvlogs_ 20170113`)

Because the pvlogs_20170310 table for one day was not created successfully. Causing problems with this pvlogs_merge_test.

About mrg_myisam making partition table. Sort it out from the Internet. Generally speaking, when the data in our database exceeds 100w records, we should consider sub-tables or partitions. This time I will elaborate on some methods of sub-tables. All the methods I know so far are MYISAM, and I don't know much about how INNODB divides tables and retains transactions and foreign keys.

First of all, we need to figure out how many tables are divided into, of course, to meet the application. Here I use a relatively simple sub-table method, that is, according to the Mantissa of self-increasing id, that is to say, a total of 10 tables with a score of 0-9, the value is also easy to do, that is, 10 is modeled. In addition, several of these bits can be divided into tables according to the md5 value of a field, so that there are many tables that can be divided. All right, let's create the table first. The code is as follows: CREATETABLE `test`.`article _ 0` (`id`BIGINT (20) NOTNULL, `content` VARCHAR (200) NOTNULL, `content`TEXTNOTNULL, PRIMARYKEY (`id`)) ENGINE = MYISAM CHARACTERSET utf8 COLLATE utf8_general_ci CREATETABLE `test`.article _ 1` (`id`BIGINT (20) NOTNULL, `contt`article (200C) NOTNULL, `content`TEXTNOTNULL, PRIMARYKEY (`id`)) ENGINE = MYISAM CHARACTERSET utf8 COLLATE utf8_general_ci CREATETABLE `test`.article _ 2` (`id`BIGINT (20) NOTNULL, `contt` VARCHAR (200C) NOTNULL, `content` TEXTNOTNULL PRIMARYKEY (`id`) ENGINE = MYISAM CHARACTERSET utf8 COLLATE utf8_general_ci CREATETABLE `test`.`article _ 3` (`id`BIGINT (20) NOTNULL, `content`NOTNULL, `content`TEXTNOTNULL, PRIMARYKEY (`id`)) ENGINE = MYISAM CHARACTERSET utf8 COLLATE utf8_general_ci CREATETABLE `test`.article _ 4` (`id`BIGINT (20) NOTNULL, `contt`VARCHAR (200i) NOTNULL, `content`TEXTNOTNULL, PRIMARYKEY (`id`)) ENGINE = MYISAM CHARACTERSET utf8 COLLATE utf8_general_ci CREATETABLE `test`.`article _ 5` (`id`BIGINT (20) NOTNULL, `content`VARCHAR NOTNULL, `content` TEXTNOTNULL) PRIMARYKEY (`id`) ENGINE = MYISAM CHARACTERSET utf8 COLLATE utf8_general_ci CREATETABLE `test`.`article _ 6` (`id`BIGINT (20) NOTNULL, `content`VARCHAR (200) NOTNULL, `content`TEXTNOTNULL, PRIMARYKEY (`id`)) ENGINE = MYISAM CHARACTERSET utf8 COLLATE utf8_general_ci CREATETABLE `test`.article _ 7` (`id`BIGINT (20) NOTNULL, `contt`article (200) NOTNULL, `content`TEXTNOTNULL, PRIMARYKEY (`id`)) ENGINE = MYISAM CHARACTERSET utf8 COLLATE utf8_general_ci CREATETABLE `test`.`article _ 8` (`id`BIGINT (20) NOTNULL, `content`VARCHAR NOTNULL, `content`TEXTNOTNULL) PRIMARYKEY (`id`) ENGINE = MYISAM CHARACTERSET utf8 COLLATE utf8_general_ci CREATETABLE `test`.`article _ 9` (`id` BIGINT (20) NOTNULL, `content` NOTNULL, `content` TEXTNOTNULL, PRIMARYKEY (`id`)) ENGINE = MYISAM CHARACTERSET utf8 COLLATE utf8_general_ci 10 tables have been created It should be noted that the id here can not be set to self-increasing, and all table structures must be consistent, including structure, type, length, and the order of fields must be the same, so how to get this id? I will explain in detail later. Now, we need a merge table for query. The code for creating the merge table is as follows: CREATETABLE `test`.`roomle` (`id`BIGINT (20) NOTNULL, `mert` VARCHAR (200) NOTNULL, `content` TEXTNOTNULL, PRIMARYKEY (`id`) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=0UNION= (`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, need to be changed to INSERT_METHOD=NO note, merge table and the previous table must also have the same structure, type, length, including the order of fields must be the same 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 article table, that is to say, this table can only perform select operations

So how to do the insertion, that is, the insert operation? the first thing is to get the unique id. Here we also need a table to create the id. The code is as follows: CREATETABLE `test`.`create _ id` (`id` BIGINT (20) NOTNULL AUTO_INCREMENT PRIMARYKEY) ENGINE = MYISAM, that is, when we need to insert data, this table must generate the id value. The method of my php code is function get_AI_ID () {$sql = "insert into create_id (id) values ('')" $this- > db- > query ($sql); return$this- > db- > insertID ();} well, now suppose we are going to insert a piece of data, what should we do? Keep looking at 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);}

/ * it is used to obtain the table name according to id * / function get_Table_Name ($id) {return 'article_'.intval ($id);} it is actually very simple, right, first get id, and then get which table should be inserted according to id, and then it is very simple. I think there is no need to talk about the operation of update, just have id, then get the table name, and then do the update operation.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.