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

If mysql processes an index, it is updated. If there is no index, it is inserted (multiple columns of unique indexes). If there is no index, It is 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 the unique index is a unique index of multiple columns. Method 1: Use

 INSERT INTO ON ... DUPLICATE KEY UPDATE ...

: Create a table as follows:

[sql] 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 identical var1, var2, and var3, so we have created a unique index index_var for multiple columns, so that we can use insert into on... duplicate key update... to update data when data is inserted. If data does not exist, the data is inserted as follows:

[sql] 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, insert the record val1 = 'abcd', val2 = 0, val3 = 'xyz', value1 = 1, value2 = 2, value3 = 3. If yes, then the value of value1 is updated to value1 + 1, the value of value2 is updated to value2 + 2, and the value of value3 is updated to value3 + 3. In this way, there is no problem, but if the table is created as follows:

[sql] 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:

[sql] 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, that is, InnoDB's performance is not as good as MyISAM. If you want to avoid performance sacrifice, 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:

[sql] 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:

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

 

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

[sql] INSERT INTO `test_table` SELECT 0, 'abcd', 0, 'xyz', 1, 2, 3  FROM 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:

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

 

OK! This is basically the end of the lecture.