MySQL 使用一条更新语句更新多条记录 [英] MySQL Update multiple records with One Update Statement

问题描述

我有一个动态生成的 UPDATE 语句，它看起来像这样:

I have a dynamically generated UPDATE Statements from which looks like this:

UPDATE `maildespatch` SET `RL_TktNumber`= Null ,`SxRemote_IP`= '::1' ,`SxRemotename`= 'Raks' WHERE `Table_ID` = 1 , 
UPDATE `maildespatch` SET `RL_TktNumber`= Null ,`SxRemote_IP`= '::1' ,`SxRemotename`= 'Raks' WHERE `Table_ID` = 2 , 
UPDATE `maildespatch` SET `RL_TktNumber`= Null ,`SxRemote_IP`= '::1' ,`SxRemotename`= 'Raks' WHERE `Table_ID` = 5 , 
UPDATE `maildespatch` SET `RL_TktNumber`= Null,`SxRemote_IP`= '::1' ,`SxRemotename`= 'Raks' WHERE `Table_ID` = '68'

数据来自用户对 HTML 表字段的输入，这些语句是基于这些语句通过 ajax() 调用动态生成的.有没有办法更一般地保存所有这些数据以在 MySQL 中保存/更新?我不想创建一个临时的 MySQL 表来保存数据，然后更新主表.

The data comes from the user-input on HTML Table Fields based on which these statements are generated dynamically through ajax() call. Is there a way to save all this data to be saved/Updated in MySQL more generically? I do not want to create a temporary MySQL Table to save data and then from update the Main Table.

推荐答案

更通用的方法是在 mysql 中调用存储过程(又名用户定义函数 - UDF)并传递必要的参数.

A more generic approach would be calling a stored procedure (a.k.a user defined function - UDF) in mysql and passing the necessary parameters.

存储过程是数据库中可重复使用的一段代码(如后端代码中的函数)，可以封装一段逻辑.查看存储过程的 MYSQL 文档 这里.

Stored procedures are reusable piece of code on your database (like functions in your backend code) that can encapsulate a piece of logic. Take a look at MYSQL documentation for stored procedures here.

使用它们，您可以随心所欲地通用 - 例如，为最常传递的值设置默认值(例如您的 SxRemote_IP = '::1' ,SxRemotename = 'Raks')这样您就不必通过网络发送它们并在前端代码中省略它们.

Using them, you can be as generic as you want - for example have defaults for values that are passed most often (like your SxRemote_IP = '::1' ,SxRemotename = 'Raks')so you don't have to send them over the network and omit them in your front end code.

这篇关于MySQL 使用一条更新语句更新多条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！