在tidb中，一个无pk/uk/ui的表，存在重复数据（完全相同的多行），我想只删除掉其中一行，我发现mysql的limit 1语法不行

🪐 TiDB 技术问题
1 
mysql> create table t_duplicat_row(c1 int,c2 int,c3 int);
Query OK, 0 rows affected (0.54 sec)

mysql> insert into t_duplicat_row values(1,1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_duplicat_row values(1,1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_duplicat_row values(1,1,1);
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> 
mysql> delete t_duplicat_row where c1=1 and c2=1 and c3=1 limit 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 27 near "where c1=1 and c2=1 and c3=1 limit 1" 
mysql> select tidb_version();
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v8.1.1
Edition: Community
Git Commit Hash: a7df4f9845d5d6a590c5d45dad0dcc9f21aa8765
Git Branch: HEAD
UTC Build Time: 2024-08-22 05:50:03
GoVersion: go1.21.13
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
2

用rowid，应该可以删除重复数据

1 个赞
3

语法不对，delete 漏了个from

1 个赞
5

select *,_tidb_rowid from t_duplicat_row
tidb 你可以利用 _tidb_rowid 来区分不同的行

2 个赞
6

此话题已在最后回复的 7 天后被自动关闭。不再允许新回复。