java - 在必须对一组数据进行排序之后逐行更新mysql

我在没有主键的MySQL Db中有一个像这样的表table1：

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| value   | varchar(25) | YES  |     | NULL    |       |
| my_date | date        | YES  | MUL | NULL    |       |
| my_time | time        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

在按my_date和my_time值对表格进行排序之后，
更新ID值是否从给定值开始？

例如从这组价值

+------+------------+----------+
| id   | my_date    | my_time  |
+------+------------+----------+
|    0 | 2018-03-01 | 09:02:00 |
|    0 | 2018-03-01 | 09:01:00 |
|    0 | 2018-03-01 | 09:00:00 |
+------+------------+----------+

我想获得

+------+------------+----------+
| id   | my_date    | my_time  |
+------+------------+----------+
|  100 | 2018-03-01 | 09:00:00 |
|  101 | 2018-03-01 | 09:01:00 |
|  102 | 2018-03-01 | 09:02:00 |
+------+------------+----------+

我已经尝试过类似的尝试，但没有成功：

try {
    int idFromToStart = 100;
    String query = "SELECT IFNULL(id, 0) FROM table1 WHERE my_date > '2018-02-28' order by my_date, my_time";
    preparedStatement =con.prepareStatement(query, ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = preparedStatement.executeQuery(query);
    System.out.println("query " + query);
    if (rs.next()) {
        rs.updateLong(1, idFromToStart);
        System.out.println("rs.getLong(1)) "+rs.getLong(1)+ " idFromToStart "+idFromToStart);
        idFromToStart++;
    }
} catch (SQLException ex) {
    System.out.println("Error db " + ex.getMessage() + " " + ex.toString());
}

最佳答案

SET @rownum = 99;
UPDATE tbl1
SET tbl1.id = @rownum:=@rownum+1
ORDER BY my_time

这是一个SQLFiddle：http://sqlfiddle.com/#!9/34699f/1

关于java - 在必须对一组数据进行排序之后逐行更新mysql，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/51946986/