L'instruction MySQL INSERT a la syntaxe "INSERT ... ON DUPLICATE KEY UPDATE". C'est beaucoup plus pratique que d'écrire les instructions INSERT et UPDATE séparément si vous souhaitez insérer ou mettre à jour un enregistrement s'il est dupliqué. Dans cet article, nous résumerons comment utiliser un tel "INSERT ... ON DUPLICATE KEY UPDATE", des points utiles et d'autres spécifications.
Utilisez un tableau comme celui ci-dessous.
select version();
+-----------+
| version() |
+-----------+
| 8.0.17 |
+-----------+
CREATE TABLE users (
id INT(8) NOT NULL,
name VARCHAR(128),
age INT(3),
registered_timestamp timestamp not null default current_timestamp,
updated_timestamp timestamp not null default current_timestamp on update current_timestamp,
primary key(id)
);
INSERT INTO users (id, name, age) VALUES(1, 'taro', 20), (2, 'jiro', 18);
+----+------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:05:52 | 2020-06-05 11:05:52 |
| 2 | jiro | 18 | 2020-06-05 11:05:52 | 2020-06-05 11:05:52 |
+----+------+------+----------------------+---------------------+
Par exemple, supposons que vous souhaitiez ajouter un enregistrement avec id: 2 à cette table, mais si vous avez déjà cet enregistrement, vous souhaitez réécrire son contenu. Vérifiez d'abord si l'enregistrement avec l'id: "2" existe, s'il n'existe pas, puis INSERT, si c'est le cas, puis l'instruction UPDATE. Le SQL requis est le suivant.
SELECT * FROM users where id = 2;
--Si aucun résultat n'est renvoyé
INSERT INTO users (id, name, age) values (2, 'jiro', 19);
--Quand le résultat est retourné
UPDATE users SET age = 19 where id = 2;
Essayons de construire et d'exécuter cette requête à partir de Java en utilisant des espaces réservés.
//Obtenir la connexion de l'instance Connection avec MySQL
String sqlSelect = "SELECT * FROM users where id = ?;";
PreparedStatement psSelect = connection.prepareStatement(sqlSelect);
psSelect.setInt(1, 2);
ResultSet rs = psSelect.excuteQuery();
if(!rs.next()){
String sqlInsert = "INSERT INTO users (id, name, age) values (?, ?, ?);";
PreparedStatement psInsert = connection.prepareStatement(sqlInsert);
psInsert.setInt(1, 2);
psInsert.setString(2, "jiro");
psInsert.setInt(3, 19);
psInsert.excute();
} else {
String sqlUpdate = "UPDATE users SET age = ? where id = ?;";
PreparedStatement psUpdate = connection.prepareStatement(sqlUpdate);
psUpdate.setInt(1, 19);
psUpdate.setInt(2, 2);
psUpdate.excute();
}
connection.commit();
C'est assez ennuyeux.
Parce que "INSERT ... ON DUPLICATE KEY UPDATE" fait un tel branchement conditionnel sur la requête
--S'il existe un enregistrement cible (instruction SELECT) --Insert sinon (instruction INSERT) --Mise à jour s'il y a (instruction UPDATE)
Il rassemble les trois requêtes de.
INSERT INTO users (id, name, age) values (2, 'jiro', 19) ON DUPLICATE KEY UPDATE age = 19;
+----+------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:05:52 | 2020-06-05 11:05:52 |
| 2 | jiro | 19 | 2020-06-05 11:05:52 | 2020-06-05 11:06:24 |
+----+------+------+----------------------+---------------------+
Dans ce cas, puisque la colonne id est la clé primaire, s'il y a un identifiant en double dans la partie "INSERT INTO", cet enregistrement sera mis à jour automatiquement. Quand ceci est assemblé à partir de Java
String sql = "INSERT INTO users (id, name, age) values (?, ?, ?) "+
"ON DUPLICATE KEY UPDATE age = ?;";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setInt(1, 2);
ps.setString(2, "jiro");
ps.setInt(3, 19);
ps.setInt(4, 2);
ps.excute();
connection.commit();
C'est définitivement plus facile.
De plus, une fonction appelée VALUES (), qui ne peut être utilisée qu'avec "ON DUPLICATE KEY UPDATE", facilite la construction des requêtes. Vous pouvez utiliser cette fonction comme suit pour faire référence à la valeur que vous avez essayé d'insérer dans la partie INSERT.
-- VALUES(age)Renvoie 19
INSERT INTO users (id, name, age) values (2, 'jiro', 19) ON DUPLICATE KEY UPDATE age = VALUES(age);
De plus, cette fonction vous permet d'insérer / mettre à jour plusieurs lignes avec "ON DUPLICATE KEY UPDATE".
-- VALUES(age)Est id:"1"Dans la ligne de 20, id:"3"Renvoie 17 sur la ligne
INSERT INTO users (id, name, age) values (1, 'taro', 20), (3, 'saburo', 17) ON DUPLICATE KEY UPDATE age = VALUES(age);
+----+--------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+--------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:05:52 | 2020-06-05 11:05:52 |
| 2 | jiro | 19 | 2020-06-05 11:05:52 | 2020-06-05 11:06:24 |
| 3 | saburo | 17 | 2020-06-05 11:07:01 | 2020-06-05 11:07:01 |
+----+--------+------+----------------------+---------------------+
Comme mentionné ci-dessus, s'il existe un doublon dans la clé primaire, il peut être mis à jour en fonction de la valeur d'âge que vous avez essayé d'insérer dans chaque enregistrement.
De plus, comme vous pouvez le voir ici, lorsque vous essayez d'exécuter UPDATE en raison de clés dupliquées, si la valeur que vous avez essayé de mettre à jour et toutes les valeurs existantes correspondent, UPDATE n'est pas exécuté et "ON UPDATE" est ajouté. La colonne n'est pas non plus mise à jour. (Identique aux spécifications de UPDATE lui-même)
Si vous essayez de construire une requête par programme sans utiliser "ON DUPLICATE KEY UPDATE" pour cette opération, vous devrez boucler le modèle lui-même, ce qui crée une branche conditionnelle à l'aide de l'instruction SELECT ci-dessus, plusieurs fois. Cependant, si vous utilisez la fonction VALUES () avec "ON DUPLICATE KEY UPDATE", vous n'avez qu'à boucler l'opération d'affectation à l'espace réservé comme indiqué ci-dessous.
int numRecord = 2;
List<String> placeholderList = new ArrayList<>();
for (int i = 0; i < numRecord; i++){
placeholdersList.add("(?, ?, ?)");
}
String sql = "INSERT INTO users (id, name, age) values "+
StringUtils.join(placeholdersList, ", ") +
"ON DUPLICATE KEY UPDATE age = VALUES(age)";
PreparedStatement ps = connection.prepareStatement(sql);
//Vous n'avez qu'à boucler cette partie pour le nombre d'enregistrements
//Premier enregistrement
ps.setInt(1, 1);
ps.setString(2, "taro");
ps.setInt(3, 20);
//Deuxième enregistrement
ps.setInt(5, 3);
ps.setString(6, "saburo");
ps.setInt(7, 17);
ps.excute();
connection.commit();
L'opération de basculement entre INSERT et UPDATE en vérifiant si chaque enregistrement existe déjà dans la base de données peut maintenant être effectuée avec une seule requête.
[Fonction de flux de contrôle IF / CASE](https://dev.mysql.com/doc/refman/5.6/ja/control-flow-functions.html] avec la syntaxe "ON DUPLICATE KEY UPDATE" ) Peut être utilisé pour les mises à jour conditionnelles.
+----+--------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+--------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:05:52 | 2020-06-05 11:05:52 |
| 2 | jiro | 19 | 2020-06-05 11:05:52 | 2020-06-05 11:06:24 |
| 3 | saburo | 17 | 2020-06-05 11:07:01 | 2020-06-05 11:07:01 |
+----+--------+------+----------------------+---------------------+
--La valeur de l'âge que vous avez essayé d'insérer(VALUES(age))Mis à jour uniquement si est supérieur à la valeur d'âge d'origine
INSERT INTO users (id, name, age) values (2, 'jiro', 20), (3, 'sabro', 17) ON DUPLICATE KEY UPDATE age = IF(VALUES(age) > age, VALUES(age), age);
+----+--------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+--------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:05:52 | 2020-06-05 11:05:52 |
| 2 | jiro | 20 | 2020-06-05 11:05:52 | 2020-06-05 11:07:38 |
| 3 | saburo | 17 | 2020-06-05 11:07:01 | 2020-06-05 11:07:01 |
+----+--------+------+----------------------+---------------------+
--La valeur de l'âge que vous avez essayé d'insérer(VALUES(age))Si est supérieur à la valeur d'âge d'origine, ajoutez 1
INSERT INTO users (id, name, age) values (2, 'jiro', 22), (3, 'saburo', 15)
ON DUPLICATE KEY UPDATE
age =
CASE
WHEN VALUES(age) > age THEN age + 1
WHEN VALUES(age) = age THEN age
WHEN VALUES(age) < age THEN VALUES(age)
END;
+----+--------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+--------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:05:52 | 2020-06-05 11:05:52 |
| 2 | jiro | 21 | 2020-06-05 11:05:52 | 2020-06-05 11:08:06 |
| 3 | saburo | 15 | 2020-06-05 11:07:01 | 2020-06-05 11:08:06 |
+----+--------+------+----------------------+---------------------+
Article qui stipule que s'il y a une colonne de numérotation automatique (AUTO_INCREMENT) dans le tableau, le numéro de série avancera de un même si UPDATE est exécuté sans mise à jour lors de l'utilisation de la syntaxe "ON DUPLICATE KEY UPDATE". Sort souvent. (Avantages et précautions de INSERT ON DUPLICATE KEY UPDATE etc.)
Mais dans la référence MySQL version 5.6
(Ces effets ne sont pas les mêmes pour les tables InnoDB où a est une colonne à incrémentation automatique. Si vous utilisez une colonne à incrémentation automatique, l'instruction INSERT augmente la valeur d'incrémentation automatique, mais pas UPDATE.)
Il y a [^ 1].
[^ 1]: La référence de la version 8 que j'utilise indique également "(Les effets ne sont pas identiques pour une table InnoDB où a est une colonne auto-incrémentée. Avec une colonne auto-incrémentée, une instruction INSERT augmente l'auto-" incrémenter la valeur mais UPDATE ne le fait pas.) ".
J'ai essayé de vérifier.
CREATE TABLE users_increment (
id INT(8) NOT NULL AUTO_INCREMENT,
name VARCHAR(128),
age INT(3),
registered_timestamp timestamp not null default current_timestamp,
updated_timestamp timestamp not null default current_timestamp on update current_timestamp,
primary key(id)
);
INSERT INTO users_increment (name, age) VALUES('taro', 20), ('jiro', 18);
+----+------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:17:58 | 2020-06-05 11:17:58 |
| 2 | jiro | 18 | 2020-06-05 11:17:58 | 2020-06-05 11:17:58 |
+----+------+------+----------------------+---------------------+
--Instruction INSERT non exécutée car la clé est dupliquée
--Si le numéro de série automatique avance même après UPDATE, l'identifiant du prochain enregistrement INSERT est"4"Devrait être
INSERT INTO users_increment (id, name, age) values (2, 'jiro', 19) ON DUPLICATE KEY UPDATE age = VALUES(age);
+----+------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:17:58 | 2020-06-05 11:17:58 |
| 2 | jiro | 19 | 2020-06-05 11:17:58 | 2020-06-05 11:19:01 |
+----+------+------+----------------------+---------------------+
--L'enregistrement suivant réellement inséré est"3"
INSERT INTO users_increment (name, age) values ('saburo', 17);
+----+--------+------+----------------------+---------------------+
| id | name | age | registered_timestamp | updated_timestamp |
+----+--------+------+----------------------+---------------------+
| 1 | taro | 20 | 2020-06-05 11:17:58 | 2020-06-05 11:17:58 |
| 2 | jiro | 19 | 2020-06-05 11:17:58 | 2020-06-05 11:19:01 |
| 3 | saburo | 17 | 2020-06-05 11:19:46 | 2020-06-05 11:19:46 |
+----+--------+------+----------------------+---------------------+
Comme mentionné ci-dessus, dans les versions récentes de MySQL, même si "ON DUPLICATE KEY UPDATE" est effectué, le numéro de série automatique ne fonctionnera que s'il est INSERT.
Fonctions de flux de contrôle du manuel de référence MySQL 5.6
Manuel de référence MySQL 5.6 INSERT ... ON DUPLICATE KEY UPDATE Syntaxe