java - 选择更新多个值的更好解决方案

Question

我想根据不同的情况更新列中的一些值......该表具有以下详细信息，例如......

                    Date               date
                    period             int
                    subcode            varchar(3)
                    status_bits        varchar(100)
                    // status bits resemble an information based code based...the data usually stored are  2343211 where each value in digits represent an information.....

现在我必须根据不同的日期和时期更新这些值......现在考虑java程序......

我已经将详细信息存储在java中的相应变量中，例如..

                   java.util.Date date[];
                   int period[];
                   String subcode[];

                   // Here for an index i , they share the same values within a row.....

如果我想以这样的方式更新它，我想更改 varchar 中的第五个字母以表示不同的日期、周期和子代码(组合).....现在..我目前的表现是这样的...

     Connection con;
     preparedStatement ps;
     String bitstatus;



     for(i=0; i < noofupdates; i++)
     {
           ps = con.prepareStatement("select status_bits from tablename where Date = ? AND period = ? AND subcode = ? limit 0,1");
           ps.setDate(1,date[i]);
           ps.setInt(2,period[i])'
           ps.setString(3,subcode[i]);

           rs=ps.executeQuery();
           while(rs.next())
           {

                 bitstatus = rs.getString(1);
                 // performed operation to update the bit.....
                 ps=con.prepareStatment("update status_bits correspondind to the same date,field and subcode");

                 ps.executeUpdate();
           }

     }

现在我想你从程序中了解到我想根据位操作常见的不同日期和时期来更新表中的 status_bits......现在我真的知道了这些方法会进行大量查询并严重影响 mysql 性能...所以请帮助我提供一个替代想法....大约有 1000 条记录需要更新.........

Answer 1

如果您知道希望将 status_bits 字符串的第 5 个位置设置为给定(日期、周期、子代码)的特定值，则可以避免 SELECT 并仅执行更新。

更新:为了正确处理 status_bits 列中的现有值少于 4 个字符的情况，我们希望确保在现有字符串为第 5 个字符时替换第 5 个字符，而不是第 2 个字符仅 1 个字符长。我们可以使用CASE表达式来进行测试。当我们至少有 4 个字符时，我们可以在前 4 个字符之后附加(所需的)第 5 个字符。否则，在附加(所需的)第 5 个字符之前，我们需要将现有字符串“填充”为 4 个字符。 (以前，该语句仅包含适用于长度至少为 4 个字符的现有字符串的表达式。)

UPDATE tablename
   SET status_bits = 
       CASE WHEN CHAR_LENGTH(status_bits >= 4
         THEN CONCAT(LEFT(status_bits,4), ?, SUBSTR(status_bits,6))
         ELSE CONCAT(RPAD(status_bits,4), ? )
         END
 WHERE `Date` = ? AND period = ? AND subcode = ? 

(如果您不想更新所有匹配的行，可以添加 LIMIT 子句，但如果您想更新所有匹配的行，则不需要这样做。If (date,period,subcode) 是唯一的，您最多会匹配一行。)

还可以根据其他条件设置 status_bits 的第 5 个位置，例如，通过向 where 子句添加谓词，或在 SET 中添加 CASE 表达式(或 IF 函数)。

UPDATE tablename
   SET status_bits = CONCAT(LEFT(status_bits,4)
                     , IF(SUBSTR(status_bits,1,1 = '1', ?, SUBSTR(status_bits,5,1))
                     , SUBSTR(status_bits,6))
 WHERE `Date` = ? AND period = ? AND subcode = ? 

相当于:

UPDATE tablename
   SET status_bits = CONCAT(LEFT(status_bits,4), ?, SUBSTR(status_bits,6))
 WHERE `Date` = ? AND period = ? AND subcode = ? 
   AND SUBSTR(status_bits,1,1) = '1'

此方法会将您正在运行的查询数量减少一半(减少与数据库的往返次数)，并避免您必须返回整个 status_bits 列。

(不幸的是，替换 varchar 列的第 5 个字符的 SQL 文本有点笨拙，因为 MySQL 没有 SQL Server 提供的更紧凑的 STUFF 函数。)

<小时/>

更新:需要更复杂的表达式来处理长度小于 4 个字符的现有 status_bits 字符串...)

UPDATE tablename
   SET status_bits = 
       CASE WHEN CHAR_LENGTH(status_bits) >= 4
            THEN CONCAT(LEFT(status_bits,4), ? , SUBSTR(status_bits,6))
            ELSE CONCAT(RPAD(status_bits,4,' '), ? )
       END
 WHERE `Date` = ? AND period = ? AND subcode = ?

注意:在该 SQL 语句中，需要在两个单独的位置参数中提供要放置在第 5 个位置的字符的值。

注意: