Mysql merge table introduction, mysqlmerge table

Mysql merge table introduction, mysqlmerge table

In Mysql databases, Mysql Merge tables are somewhat similar to views. Next let's take a look at the advantages of Mysql Merge tables and hope to help you.

Advantages of Mysql Merge table:
A: Separate Static and Dynamic Data
B: optimize queries using data with close Structures
C: less data can be accessed during query.
D: It is easier to maintain large datasets.
E: You can modify the. mrg file to modify the Merge table. You can also use alter to modify the table. After modification, you must use flush tables to refresh the table cache. This method can dynamically increase or decrease the number of sub-TABLES.
Creation method, for example:
Mysql> create table t1 (a int not null primary key) ENGINE = MyISAM;
Mysql> create table t2 (a int not null primary key) ENGINE = MyISAM;
Mysql> create table mrg (a int not null primary key) ENGINE = merge union = (t1, t2) INSERT_METHOD = LAST;
Add test data below
Mysql> insert into t1 (a) VALUES (1), (2 );
Mysql> insert into t2 (a) VALUES (1), (2 );
Query the result
Mysql> SELECT a FROM mrg;
The data in tables t1 and t2 is displayed.
+ ------ +
| A |
+ ------ +
| 1 |
| 2 |
| 1 |
| 2 |
+ ------ +

For merge tables, note that
1. The structure of each sub-table must be consistent, and the structure of the master table and sub-table must be consistent,
2. The index of each sub-table exists in the merge table. Therefore, you cannot perform a unique search based on the index in the merge table.
3. The sub-table must be the MyISAM engine.
4. REPLACE does not work in the merge table
5 AUTO_INCREMENT will not work as expected.

The INSERT_METHOD parameter used to create a Mysql Merge table has several parameters.
LAST if you execute the insert command to operate the merge table, the insert operation adds the data to the LAST sub-table. Similarly, when inserting data, the data is added to the FIRST sub-table.
For example, in this example, the insert operation is performed on the merge table.
Mysql> insert into mrg (a) VALUES (18 );
Query
Mysql> SELECT a FROM t2;
As a result, 18 is displayed in table t2.
---------------------------------------------------------------
If you perform the DROP operation on the mrg table or sub-table, unexpected conditions may occur.
If you delete a mrg table, there is no association between the sub-tables. However, if you delete any of the sub-tables, the merge table structure and data still exist for GNU/LINUX.
Mysql> drop table t1, t2;
Mysql> SELECT a FROM mrg;
You will find that the query results of the mrg table remain unchanged.

It queries multiple tables logically as one table. There are two files after the creation,
. Frm table structure definition
. Mrg union table name list

--
-- Structure of the merger table 'test _ merge'
--

Create table if not exists 'test _ merge '(
'Id' int (5) not null auto_increment,
'Names' varchar (100) not null,
Primary key ('id ')
) ENGINE = MRG_MyISAM default charset = gb2312 INSERT_METHOD = last union = ('test _ merge_1 ', 'test _ merge_2', 'test _ merge_3 ');

--
-- Export the table data 'test _ merge'
--

Insert into 'test _ merge' ('id', 'names') VALUES
(1, 'A '),
(1, 'bb '),
(1, 'cc ');

----------------------------------------------------------

--
-- Structure of the basic table 'test _ merge_1'
--

Create table if not exists 'test _ merge_1 '(
'Id' int (5) not null auto_increment,
'Names' varchar (100) not null,
Primary key ('id ')
) ENGINE = MyISAM default charset = gb2312 AUTO_INCREMENT = 3;

--
-- Export the table data 'test _ merge_1'
--

Insert into 'test _ merge_1 '('id', 'names') VALUES
(1, 'A ');

----------------------------------------------------------

--
-- Basic table structure 'test _ merge_2'
--

Create table if not exists 'test _ merge_2 '(
'Id' int (5) not null auto_increment,
'Names' varchar (100) not null,
Primary key ('id ')
) ENGINE = MyISAM default charset = gb2312 AUTO_INCREMENT = 2;

--
-- Export the table data 'test _ merge_2'
--

Insert into 'test _ merge_2 '('id', 'names') VALUES
(1, 'bb ');

----------------------------------------------------------

--
-- Structure of the basic table 'test _ merge_3'
--

Create table if not exists 'test _ merge_3 '(
'Id' int (5) not null auto_increment,
'Names' varchar (100) not null,
Primary key ('id ')
) ENGINE = MyISAM default charset = gb2312 AUTO_INCREMENT = 2;

--
-- Export the table data 'test _ merge_3'
--

Insert into 'test _ merge_3 '('id', 'names') VALUES
(1, 'cc ');
1. This table is similar to the union mechanism in SQL.
2. The structure of the table must be exactly the same as that of the basic table, including the column name and sequence. The UNION table must belong to the same DATABASE.
3. The basic table type must be MyISAM.
4. You can modify the. mrg file to modify the MERGE table. Each basic table name occupies one row. Note: After modification, use flush tables to refresh the table cache.
5. Changes to the basic table can be directly reflected in this table.
6. The value of INSERT_METHOD can be: 0. inserting FIRST into the FIRST table in the UNION is not allowed to be inserted to the LAST table in the UNION. (Available after 4.0)
7. the constraint defined on it does not have any effect. The constraint is controlled by the basic table. For example, two basic tables have the same Key value, then there will be two identical Key values in the MERGE table.

MYSQL multi-Table query as a table

You can create a merge table to meet your requirements.
However, TB1 and TB2 must be myisam
The following is an example.
Mysql> create table t1 (a int not null primary key) ENGINE = MyISAM;
Mysql> create table t2 (a int not null primary key) ENGINE = MyISAM;
Mysql> create table mrg (a int not null primary key) ENGINE = merge union = (t1, t2)

Help: mysql multi-Table Association update !!!

During database design, you should no longer define the zongping field for the above table, because the value of this field is completely calculated by other fields, saving its value on a disk is a waste of space.

To facilitate data search, we should define a table that only contains three fields: pingshi, qizhong, and qimo, and then define a VIEW that contains calculated fields, if the table name is tb, The Definition Statement of the view vw is:

Create view vw (pingshi, qizhong, qimo, zongping)
As select pingshi, qizhong, qimo, pingshi * 0.1 + qizhong * 0.3 + qimo * 0.6 FROM tb

In this way, you can query the vw view. The data is the same as the tb table, but the calculated field is correct.

The statement for creating a view in MYSQL is:

Create view syntax
CREATE [or replace] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] check option]
This statement can create a new view. If the or replace clause is specified, this statement can REPLACE the existing view. Select_statement is a SELECT statement that defines a view. This statement can be selected from the base table or other views.

This statement requires the create view permission for the VIEW and some permissions for each column selected by the SELECT statement. Columns used elsewhere in the SELECT statement must have the SELECT permission. If there is an or replace clause, you must have the DROP permission on The View.

View is a database. By default, a new view is created for the current database. To explicitly create a view in a given database, you must specify the name db_name.view_name when creating the view.

Mysql> create view test. v as select * FROM t;
Tables and views share the same namespace in the database. Therefore, the database cannot contain tables and views with the same name.

A view must have a unique column name and must not have duplicates, just like a base table. By default, the column names retrieved by the SELECT statement are used as View column names. To define a clear name for a View column, use the optional column_list clause to list IDs separated by commas. The number of names in column_list must be equal to the number of columns retrieved by the SELECT statement.

The columns retrieved by the SELECT statement can be simple references to the table columns. You can also use expressions such as functions, constant values, and operators.

Unqualified tables or views in the SELECT statement are interpreted according to the default database. By specifying a table or view name with an appropriate database name, a view can reference a view in a table or other database.

Multiple SELECT statements can be used to create views. A view can reference a base table or other views. It can use UNION, UNION, and subquery. SELECT does not even need to reference any table. In the following example, a view is defined to select two columns from the other table, and the expressions calculated based on these columns are given:

Mysql> & nb... the remaining full text>