MySQL optimization - specific analysis of additions, deletions and modifications (picture)

MySQL optimization - specific analysis of additions, deletions and modifications (picture)

Insert

will be more Row query results are inserted into the table

Syntax

INSERT INTO table_name1(column_list1) SELECT (column_list2) FROM table_name2 WHERE (condition)

table_name1 specifies the table into which data is to be inserted; column_list1 specifies which columns in the table to be inserted into which data is to be inserted; table_name2 specifies the data to be inserted from

Which table is queried from; column_list2 specifies the query column of the data source table. This list must have the same number of fields and the same data type as the column_list1 list;

condition specifies the query condition of the SELECT statement

Query all records from the person_old table and insert them into the person table

CREATE TABLE person (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  NAME CHAR(40) NOT NULL DEFAULT '',
  age INT NOT NULL DEFAULT 0,
  info CHAR(50) NULL,
  PRIMARY KEY (id)
)

CREATE TABLE person_old (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  NAME CHAR(40) NOT NULL DEFAULT '',
  age INT NOT NULL DEFAULT 0,
  info CHAR(50) NULL,
  PRIMARY KEY (id)
)

INSERT INTO person_old
VALUES (11,'Harry',20,'student'),(12,'Beckham',31,'police')

SELECT * FROM person_old

You can see that the records were inserted successfully and the person_old table now has Two records. Next, insert all the records in the person_oldperson_old table into the person table

INSERT INTO person(id,NAME,age,info)
SELECT id,NAME,age,info FROM person_old;
SELECT * FROM person

You can see that the data transfer is successful. The id field here is For auto-incremented primary keys, the uniqueness of the field value must be ensured when inserting. If it is not determined, the field can be ignored when inserting.

Only insert the values ​​of other fields

If executed again An error will occur once

The difference between MYSQL and SQLSERVER:

Difference 1

When there are duplicate values ​​in the data to be imported, MYSQL will have three options

Option 1: Use the ignore keyword
Option 2: Use replace into
Option Three: ON DUPLICATE KEY UPDATE

The second and third options will not be introduced here, because they are more complicated and do not meet the requirements. Only the first option will be discussed here

TRUNCATE TABLE person

TRUNCATE TABLE persona_old 

INSERT INTO person_old
VALUES (11,'Harry',20,'student'),(12,'Beckham',31,'police')

##注意下面这条insert语句是没有ignore关键字的
INSERT  INTO person(id,NAME,age,info)
SELECT id,NAME,age,info FROM person_old;

INSERT INTO person_old 
VALUES (13,'kay',26,'student')

##注意下面这条insert语句是有ignore关键字的
INSERT IGNORE INTO person(id,NAME,age,info)
SELECT id,NAME,age,info FROM person_old;

You can see that the insertion is successful

SQLSERVER

On the SQLSERVER side, if you want to ignore duplicate keys, you need Specify WITH (IGNORE_DUP_KEY=ON) when creating the table ON [ PRIMARY]

In this way, when inserting duplicate values, SQLSERVER will retain the value for the first time, and when duplicate values ​​are found for the second time, SQLSERVER will ignore it. Get rid of

Difference 2

The difference when inserting an auto-increment column

SQLSERVER You need to use SETIDENTITY_INSERT table nameON to insert the value of the auto-increment field into the table. If you do not add SET IDENTITY_INSERT table name ON

When inserting data into the table, the value of the auto-increment field cannot be specified. The value of the id field cannot be specified. SQLSERVER will automatically increase it for you.一

INSERTINTO person(NAME,age,info) VALUES ('feicy',33,'student')

But MYSQL does not need it, and the degree of freedom is very large

you You can specify the value of the id field as NULL, and MYSQL will automatically add one for you.

INSERTINTO person(id,NAME,age,info) VALUES (NULL,'feicy',33,'student')

can also be used You can also specify the value

INSERT IGNORE INTO person(id,NAME,age,info) VALUES (16,'tom',88,'student')

without writing the value of the id. MYSQL will automatically add one for you.

INSERT IGNORE INTO person(NAME,age,info) VALUES ('amy',12,'bb')

You can specify the value of the id field or not. When specifying, as long as the current id field column does not have the value you are inserting, that is, there will be no duplicate values.

The degree of freedom is very large, and there is no need to specify SET IDENTITY_INSERT table name ON Options

Difference three

NULL value duplication problem of unique index

MYSQL

In MYSQL, the UNIQUE index will be invalid for null fields

insert into test(a) values(null)
 
insert into test(a) values(null)

The above insert statement can be inserted repeatedly (the same is true for the joint unique index)

SQLSERVER

SQLSERVER will not work

CREATE TABLE person (
  id INT  NOT NULL IDENTITY(1,1),
  NAME CHAR(40)  NULL DEFAULT '',
  age INT NOT NULL DEFAULT 0,
  info CHAR(50) NULL,
  PRIMARY KEY (id)
)

CREATE UNIQUE INDEX IX_person_unique ON [dbo].[person](name)

INSERT INTO [dbo].[person]
        ( [NAME], [age], [info] )
VALUES  ( NULL, -- NAME - char(40)
          1, -- age - int
          'aa'  -- info - char(50)
          ),
          ( NULL, -- NAME - char(40)
          2, -- age - int
          'bb'  -- info - char(50)
          )

消息 2601，级别 14，状态 1，第 1 行
不能在具有唯一索引“IX_person_unique”的对象“dbo.person”中插入重复键的行。重复键值为 (<NULL>)。
语句已终止。

---

Update

The update is relatively simple, not much Said

UPDATE person SET info ='police' WHERE id BETWEEN 14 AND 17

SELECT * FROM person

---

删除

删除person表中一定范围的数据

DELETE FROM  person  WHERE id BETWEEN 14 AND 17

SELECT * FROM person

如果要删除表的所有记录可以使用下面的两种方法

##方法一
DELETE     FROM person

##方法二
TRUNCATE TABLE  person

跟SQLSERVER一样，TRUNCATE TABLE会比DELETE FROM TABLE 快

MYISAM引擎下的测试结果，30行记录

跟SQLSERVER一样，执行完TRUNCATE TABLE后，自增字段重新从一开始。

################################
INSERT IGNORE INTO person(id,NAME,age,info)
SELECT id,NAME,age,info FROM person_old;

SELECT * FROM person

TRUNCATE TABLE  person


INSERT IGNORE INTO person(NAME,age,info) VALUES ('amy',12,'bb')

SELECT * FROM person

当你刚刚truncate了表之后执行下面语句就会看到重新从一开始

SHOW TABLE STATUS LIKE 'person'

The above is the detailed content of MySQL optimization - specific analysis of additions, deletions and modifications (picture). For more information, please follow other related articles on the PHP Chinese website!