如何以标准方式删除没有主键的记录(即不使用ctid)

Question

用例

我们有一个没有PK的日志表，有两列(日期、事件)；

你插了一排。然后要撤消插入的记录。

是否有一种方法可以删除该行(而不删除具有相同数据的其他行)，而不使用postgres功能？

我想要一个在另一个数据库中工作的解决方案(它不需要是完全标准的，它只能在一个数据库中运行: SqLite、Oracle、MySql或SQLServer)。

示例：

create table the_log(
  date date,
  event_id integer
);

insert into the_log(date, event_id) values ('2019-09-21',1),('2019-09-21',1);

select * from the_log;

我的赎罪：

delete from the_log where row_number() over ()=1;

delete from the_log limit 1;

with the_log_2 as (select *, row_number() over () as version from prueba_sin_clave)
delete from the_log_2 where version=1;

我认为答案是否定的。我想知道我是否错了，或者我在哪些文件中读到的我是对的。

Answer 1

警告:这可能会使您丢失数据，因此请考虑使用事务块。

我认为这是更通用的解决方案：

如果您认为要删除的行是：

select * from the_log limit 1 

如果你有一列有唯一的价值..。

delete from the_log  
where unique_the_log_column in 
(select unique_log_column from the_log limit 1); 

如果您没有唯一的列，但是有两个合并后生成唯一值的列：

delete from the_log  
where (col1, col2) in (select col1, col2 from the_log limit 1); 

其中any_the_log_column是此表中具有唯一值的列。

这可能会删除任何dbms中日志表的第一行--如果列中的数据不被重复，但请记住，有时在某些数据库中没有选择表，因此必须在此表中有一个id .

考虑创建一个id列，并增加它在表中的行数，根据日期或什么，如果你不想在未来受到伤害.

Answer 2

如果您只是想消除完全重复的行(只保留一个实例)，那么就没有一个完全与RDBMS无关的方法可以做到这一点。DELETE ... ORDER BY ... LIMIT在MySQL上工作，但对其他一些不起作用。在Oracle上，可以使用隐藏的ROWID进行删除。

在不使用任何专有/惯用特性的情况下，应该在几乎任何RDBMS中工作的去重复的解决方案之一是这个算法：

1. 标识完全重复的行。
2. 将复制的行内容(但每个副本只复制一份)放到临时表中。
3. 在事务中:删除原始表中重复的行。将临时表中的所有内容复制到原始表中。

下面是一个工作示例(用于MySQL)。只要有少量的语法变化，就可以在任何RDBMS上完成这项工作。

CREATE TABLE the_log (
  date date,
  event_id integer
);

INSERT INTO the_log (date, event_id)
  VALUES ('2019-09-21',1),('2019-09-21',1);

SELECT * FROM the_log;

从两个完全重复的行开始。

+------------+----------+
| date       | event_id |
+------------+----------+
| 2019-09-21 |        1 |
| 2019-09-21 |        1 |
+------------+----------+

CREATE TEMPORARY TABLE the_log_duplicates LIKE the_log;

INSERT INTO the_log_duplicates
  SELECT date, event_id
  FROM the_log
  GROUP BY date, event_id
  HAVING count(*) > 1;

START TRANSACTION;

DELETE the_log
  FROM the_log
  JOIN the_log_duplicates
  USING (date, event_id);

INSERT INTO the_log
  SELECT * FROM the_log_duplicates;

COMMIT;

DROP TEMPORARY TABLE the_log_duplicates;

SELECT * FROM the_log;

看，你的重复行现在是一个了。

+------------+----------+
| date       | event_id |
+------------+----------+
| 2019-09-21 |        1 |
+------------+----------+