Update if mysql processing exists, and insert if not (multiple columns of unique indexes)

When mysql processes a unique index, it updates the index. If it does not exist, it should be very common to insert the index. There are many similar articles on the Internet, today I will talk about the problems and methods that may occur when this unique index is a unique index for multiple columns. Method 1: use? INSERTINTO? ON... DUPLICATEKEYUPDATE...: The table is created as follows: C

When mysql processes a unique index, it updates the index. If it does not exist, it should be very common to insert the index. There are many similar articles on the Internet, today I will talk about the problems and methods that may occur when this unique index is a unique index for multiple columns. Method 1: use? Insert? ON... duplicate key update...: The table is created as follows: C

When mysql processes a unique index, it updates the index. If it does not exist, it should be very common to insert the index. There are many similar articles on the Internet, today I will talk about the problems and methods that may occur when this unique index is a unique index for multiple columns.

Method 1:

Use? Insert? ON... duplicate key update ...:

The table is created as follows:

CREATE TABLE `test_table` (  `id`  int(11) NOT NULL AUTO_INCREMENT ,  `var1`  varchar(100) CHARACTER SET utf8 DEFAULT NULL,  `var2`  tinyint(1) NOT NULL DEFAULT '0',  `var3`  varchar(100) character set utf8 default NULL,  `value1`  int(11) NOT NULL DEFAULT '1',  `value2`  int(11) NULL DEFAULT NULL,  `value3`  int(5) DEFAULT NULL,  PRIMARY KEY (`Id`),  UNIQUE INDEX `index_var` (`var1`, `var2`, `var3`)) ENGINE=MyISAM DEFAULT CHARACTER SET=latin1 AUTO_INCREMENT=1;

In this table, there can be only one record with the same var1, var2, and var3, so we have created a unique index index_var for multiple columns, so that we can use ?? Insert? ON... duplicate key update:

INSERT INTO `test_table` (`var1`, `var2`, `var3`, `value1`, `value2`, `value3`) VALUES ('abcd', 0, 'xyz', 1, 2, 3) ON DUPLICATE KEY UPDATE `value1` = `value1` + 1 AND `value2` = `value2` + 2 AND `value3` = `value3` + 3;

This insert statement means to insert data to the test_table table. If there is no record val1 = 'abcd', val2 = 0, val3 = 'xyz', then insert it.

Val1 = 'abcd', val2 = 0, val3 = 'xyz', value1 = 1, value2 = 2, value3 = 3 Records,

If so, update value1 to value1 + 1, value2 to value2 + 2, and value3 to value3 + 3.

In this way, there is no problem, but if the table is created as follows:

CREATE TABLE `test_table` (  `id`  int(11) NOT NULL AUTO_INCREMENT ,  `var1`  varchar(1024) CHARACTER SET utf8 DEFAULT NULL,  `var2`  tinyint(1) NOT NULL DEFAULT '0',  `var3`  varchar(1024) character set utf8 default NULL,  `value1`  int(11) NOT NULL DEFAULT '1',  `value2`  int(11) NULL DEFAULT NULL,  `value3`  int(5) DEFAULT NULL,  PRIMARY KEY (`Id`),  UNIQUE INDEX `index_var` (`var1`, `var2`, `var3`)) ENGINE=MyISAM DEFAULT CHARACTER SET=latin1 AUTO_INCREMENT=1;

Note: The maximum length of var1 and var3 is changed from 100 to 1024. When you execute this statement, the following error is returned:

Specified key was too long; max key length is 1000 bytes

This is because the index_var index is 1024*3 + 1 + 1024*3> 1000. What if this happens? There are two solutions.

First, you can change the database engine from MyISAM to InnoDB. What are the differences between the two engines?

Here

However, there is a disadvantage in this case, that is, InnoDB's performance is not as good as MyISAM's. If you want to avoid sacrificing performance, you have to use the second method, that is, method 2 we are talking about here!

Method 2:

Use dual virtual tables.

If you use dual virtual tables, you do not need to create multiple unique indexes. The table is created as follows:

CREATE TABLE `test_table` (  `id`  int(11) NOT NULL AUTO_INCREMENT ,  `var1`  varchar(1024) CHARACTER SET utf8 DEFAULT NULL,  `var2`  tinyint(1) NOT NULL DEFAULT '0',  `var3`  varchar(1024) character set utf8 default NULL,  `value1`  int(11) NOT NULL DEFAULT '1',  `value2`  int(11) NULL DEFAULT NULL,  `value3`  int(5) DEFAULT NULL,  PRIMARY KEY (`Id`)) ENGINE=MyISAM DEFAULT CHARACTER SET=latin1 AUTO_INCREMENT=1;

The insert statement is like:

INSERT INTO table(primarykey, field1, field2, ...)SELECT key, value1, value2, ...FROM dualWHERE not exists (select * from table where primarykey = id);

In this case, we can use the following statement instead:

INSERT INTO `test_table` SELECT 0, 'abcd', 0, 'xyz', 1, 2, 3FROM dual WHERE NOT EXISTS (SELECT * FROM `test_table` WHERE `var1` = 'abcd' AND `var2` = 0 AND `var3` = 'xyz');

In this case, if the record val1 = 'abcd', val2 = 0, val3 = 'xyz' does not exist, the insert statement will be executed to insert the record. If yes, then we need to use the corresponding update statement to update the record:

UPDATE `test_table` SET `value1` = `value1` + 1, `value2` = `value2` + 2, `value3` = `value3` + 3WHERE `val1` = 'abcd' AND `val2` = 0 AND `val3` = 'xyz';

OK! This is basically the end of the lecture.

Note: Please indicate the source for reprinting!

Refer:

Http://thobian.info /? P = 1035

Http://blog.51yip.com/mysql/1515.html ??

Author: Sky_qing published on 19:28:59 Original article link

Read: 64 comments: 0 view comments

Original article address: If mysql processing exists, it will be updated. If it does not exist, it will be inserted (multiple columns of unique indexes). Thank you for sharing it.