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

MYSQL DDL how to understand and application, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

DDL operation has always been a weakness of our MYSQL. In fact, the related alter statements have changed from MYSQL 5.6, that is, the inplace and copy of the topic. In fact, many people know, but use less, because there is a pt-OSC tool, there is another tool gh-ost.

The reason the maintenance now mentions this string of tofu is that MYSQL 8 is developing so fast that pt tools are a little behind. According to official documents, after 8.013, some of the PT tools will sometimes have various problems in use.

So we can still use the positive root alter statement in mysql on the high version, and it involves, when inplace, when copy, what the heck is this? MYSQL's DDL is so tiring.

Below OK is an official + test item + MGR MYSQL 8.018.

This is mainly aimed at the addition, deletion and modification of the DDL of the field.

Judging from the table provided by 8.0 above

The following is provided by mysql 5.7. you can clearly see that death is really different. There is an extra column called Instant.

But in fact, we can see that there are not many things that can be done immediately, such as adding a column, setting a column default value, Dropping the column default value, etc., but the most commonly used adding a column also has a * number on it, which means that not all of them can be done, and there must be conditions.

Conditions:

1 or you can add fields. If you mix alter table statements, you will not perform instant operations. For example, a statement will add fields and delete fields.

2 fields can only be added to the last column of the table. If you want to add any fields between them, it won't work.

3 the row_format of the table cannot be in compressed compressed format

4 there is a full-text index in the table, but no no no cannot

5 temporary tables are not allowed

6 data dictionary tables are not allowed

It's pretty fast to add fields. Let's see if adding fields is so comfortable with a new table and inserting data all the time.

Let's see how it turns out.

DROP TABLE IF EXISTS test.test

CREATE TABLE test.test (

Id int (10) not null auto_increment

Name varchar (20) not null

Age smallint not null

Work_years smallint not null

PRIMARY key (`id`)

) ENGINE INNODB DEFAULT CHARSET utf8 COMMENT 'test'

# emptying data

TRUNCATE table test.test

, /

# define stored procedures

Delimiter / /

DROP PROCEDURE IF EXISTS insert_test_val

# # the number of data to be inserted in num_limit, the maximum random value of rand_limit

CREATE PROCEDURE insert_test_val ()

BEGIN

DECLARE i int default 1

DECLARE a varchar (20)

DECLARE b smallint

DECLARE c smallint

WHILE i

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.