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

Yesterday, a friend in the WeChat group also inadvertently asked, saying that the table fields in the database want to maintain a relatively standard order, what to do? You know, this operation in Oracle is a bit complicated, because it is handled in an appended way. It is not possible to add a field 3 between the existing fields 1 and 2. But MySQL can. MySQL seems to be much more flexible in this respect. What is the reason for this? what is the difference between them in design?

There is a definition file, the frm file, for each table in MySQL. Let's take out a table and see if we can simply parse it.

For example, a table field contains the following contents:

> desc zd_warshrine_prostate

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | int (10) | NO | PRI | NULL | auto_increment |

| | proName | varchar | NO | MUL | NULL |

| | TYPE | varchar (10) | NO | | NULL |

| | loaderr | int (11) | NO | | 0 | |

| | loadTime | timestamp | NO | | CURRENT_TIMESTAMP |

+-+ +

We can use strings to simply parse it, and we can read some information through the above.

# strings zd_warshrine_prostate.frm

PRIMARY

In_ty_zyl_proName

InnoDB

)

ProName

TYPE

Loaderr

LoadTime

ProName

TYPE

Loaderr

LoadTime

In general, you can see that only the field name is parsed. When you look at the data dictionary columns in MySQL, you can't see a field like column_id at all.

To add fields sequentially in MySQL, the statement can be written as follows:

ALTER TABLE test

ADD COLUMN `amount_ sum` double (255pm 0) AFTER `amount_ name`

That is, add the field amount_sum after the field amount_name

Could it be that this way in MySQL is superior, not anymore? for operations such as adding fields and modifying data types, MySQL was also criticized in early versions because it would lock the table directly, and the idea to achieve it is actually to copy table data, similar to reconstruction. This situation has been improved in some later versions, such as 5.6, and with pt-osc 's tools, this improvement can be modified online. The implementation is actually a bit similar to online redefinition in Oracle, where MySQL creates a temporary table, then creates two triggers, then synchronizes the data to the temporary table, and then the trigger synchronizes the operation. If the table data is not large, it is not a big problem, once the data level up, business attention up, this place is worth digging.

How do you do it in Oracle? There still seems to be a big difference.

For example, let's look at the data of a table users.

SQL > select dbms_rowid.ROWID_RELATIVE_FNO (rowid) as file#,dbms_rowid.ROWID_BLOCK_NUMBER (rowid) as block#,dbms_rowid.ROWID_ROW_NUMBER (rowid) as row#,a.* from test.USERS a where rownum desc dba_tab_columns

Name Null? Type

-

OWNER NOT NULL VARCHAR2 (30)

TABLE_NAME NOT NULL VARCHAR2 (30)

COLUMN_NAME NOT NULL VARCHAR2 (30)

DATA_TYPE VARCHAR2 (106)

DATA_TYPE_MOD VARCHAR2 (3)

DATA_TYPE_OWNER VARCHAR2 (60)

DATA_LENGTH NOT NULL NUMBER

DATA_PRECISION NUMBER

DATA_SCALE NUMBER

NULLABLE VARCHAR2 (1)

COLUMN_ID NUMBER

DEFAULT_LENGTH NUMBER

DATA_DEFAULT LONG

NUM_DISTINCT NUMBER

If you add fields to a large table, if default values are involved, it will be very difficult. In addition to adding fields, Oracle still handles much better, at least does not rebuild the table data. This can be regarded as some of the differences in design between the two. There are many diagnostic methods for Oracle, but there seem to be fewer ways and means for MySQL, but there are also several ways.

For example, to verify MySQL for adding fields and modifying data types, you can check show processlist and find a thread that will mark copy to tmp table.

At the same time, two temporary files are created under the data directory, similar to the form below.

-rw-rw---- 1 mysql mysql 8860 Nov 4 19:15 # sql-2721_17a3a9.frm

-rw-rw---- 1 mysql mysql 549453824 Nov 4 19:16 # sql-2721_17a3a9.ibd

And further want to see more content, that is the source code, in fact, it's okay. I've already seen some awesome people parsing this part of the content, but I have to read it myself and digest it before I can take it out.

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.