python - SQL 行不会删除。不抛出错误

Question

我的数据库中有一行我无法删除。我在 python 中运行这个 SQL 并得到这个错误:

MariaDB [cryptotrader2]> INSERT INTO `trades` VALUES("gemini",2465337307,17317.010000,0.008923,1513178949);
    ERROR 1062 (23000): Duplicate entry 'gemini-2147483647' for key 'PRIMARY'

这很奇怪，因为我没有影响该条目。我发现只有一行 exchange='gemini' 并且我无法删除它或添加其他 exchange='gemini' 的条目。或者至少不是来 self 的 python 程序。我确实设法进行了手动输入，如下所示。我对使用其他交易所名称的条目没有问题，它只是 gemini。

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 84
Server version: 10.0.30-MariaDB-0+deb8u1 (Debian)

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [cryptotrader2]> select * from trades where exchange='gemini'
    ->
    -> ;
+----------+------------+----------+------------+------------+
| exchange | trade_id   | price    | volume     | timestamp  |
+----------+------------+----------+------------+------------+
| gemini   | 2147483647 | 17395.80 | 0.06573300 | 1513178007 |
+----------+------------+----------+------------+------------+
1 row in set (0.00 sec)

MariaDB [cryptotrader2]> delete from trades where exchange='gemini';
Query OK, 1 row affected (0.01 sec)

MariaDB [cryptotrader2]> select * from trades where exchange='gemini';
+----------+------------+----------+------------+------------+
| exchange | trade_id   | price    | volume     | timestamp  |
+----------+------------+----------+------------+------------+
| gemini   | 2147483647 | 17385.33 | 0.07917300 | 1513178234 |
+----------+------------+----------+------------+------------+
1 row in set (0.00 sec)

MariaDB [cryptotrader2]> insert into trades values ('gemini',1234564560,17000,1,1513178235);
Query OK, 1 row affected (0.00 sec)

MariaDB [cryptotrader2]> select * from trades where exchange='gemini';          +----------+------------+----------+------------+------------+
| exchange | trade_id   | price    | volume     | timestamp  |
+----------+------------+----------+------------+------------+
| gemini   | 1234564560 | 17000.00 | 1.00000000 | 1513178235 |
| gemini   | 2147483647 | 17385.33 | 0.07917300 | 1513178234 |
+----------+------------+----------+------------+------------+
2 rows in set (0.01 sec)

MariaDB [cryptotrader2]> delete from trades where exchange='gemini';            Query OK, 2 rows affected (0.00 sec)

MariaDB [cryptotrader2]> select * from trades where exchange='gemini';
+----------+------------+----------+------------+------------+
| exchange | trade_id   | price    | volume     | timestamp  |
+----------+------------+----------+------------+------------+
| gemini   | 2147483647 | 17390.16 | 0.87946700 | 1513178350 |
+----------+------------+----------+------------+------------+
1 row in set (0.00 sec)

Answer 1

它显然正在被删除，但在某处您有一个 upsert 为 gemini 创建了一个条目。查看价格、交易量和时间戳如何变化。

我应该说错误的原因是 INT 的最大有符号值为 2147483647。您尝试插入更高值 (2465337307) 的尝试显然被截断为最大值，在这种情况下是正确的，您已经有一个 gemini 条目，2147483647。尝试插入

    INSERT INTO `trades` VALUES("gemini",2147483646,17317.010000,0.008923,1513178949); 

使用你的 python 程序。