主页 / user-148211

ankit.vishen's questions

Martin Hope
ankit.vishen
Asked: 2024-08-24 13:57:17 +0800 CST
  • 5

下面是我的表结构，主键id是 的组合reportedUserId_reporteeUserId。我用它来执行INSERT IGNORE查询，我想在scanType列上实现表分区，但为此，我必须将其添加到主键中，并且插入的值会因为多个值而重复scanType，但我希望它是唯一的。

那么是否有可能在上维护唯一的记录id(reportedUserId_reporteeUserId)并在上维护表分区scanType？

+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| id             | varchar(255) | NO   | PRI | NULL    |       |
| reportedUserId | bigint       | YES  | MUL | NULL    |       |
| reporteeUserId | bigint       | YES  | MUL | NULL    |       |
| createdTime    | datetime     | YES  |     | NULL    |       |
| remarks        | varchar(255) | YES  |     | NULL    |       |
| contestId      | bigint       | YES  |     | NULL    |       |
| scanType       | int          | NO   |     | 1       |       |
+----------------+--------------+------+-----+---------+-------+
mysql
Martin Hope
ankit.vishen
Asked: 2022-02-22 23:03:34 +0800 CST
  • 0

我有一个包含 95929357 条记录的 MySQL 表，下面是相同的表模式

CREATE TABLE `Friends` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `createdTime` datetime DEFAULT NULL,
  `friendUserId` bigint(20) DEFAULT NULL,
  `friendshipStatus` int(11) DEFAULT NULL,
  `remarks` varchar(255) DEFAULT NULL,
  `updatedTime` datetime DEFAULT NULL,
  `userId` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `userId` (`userId`,`friendUserId`),
  KEY `createdTime` (`createdTime`)
) ENGINE=InnoDB AUTO_INCREMENT=1321597934 DEFAULT CHARSET=latin1

我有两个问题要问

问题1# 如果只有95929357条记录，AUTO_INCREMENT怎么增加到1321597934（注意-条目是由默认值为0的代码插入的）。我还注意到，id 存在差距（exp- 从 id 50001124 到新的 id 50001393）

| 50001122 | 2021-09-19 03:33:06 |    116176872 |                0 | NULL    | 2021-09-19 03:33:06 | 103802246 |
| 50001123 | 2021-09-19 03:33:06 |    114361519 |                0 | NULL    | 2021-09-19 03:33:06 | 116176872 |
| 50001124 | 2021-09-19 03:33:06 |    116176872 |                0 | NULL    | 2021-09-19 03:33:06 | 114361519 |
| 50001393 | 2021-09-19 03:33:07 |    109233877 |                0 | NULL    | 2021-09-19 03:33:07 | 125715813 |
| 50001394 | 2021-09-19 03:33:07 |    125715813 |                0 | NULL    | 2021-09-19 03:33:07 | 109233877 |
| 50001395 | 2021-09-19 03:33:07 |    105588884 |                0 | NULL    | 2021-09-19 03:33:07 | 125715813 |
| 50001396 | 2021-09-19 03:33:07 |    125715813 |                0 | NULL    | 2021-09-19 03:33:07 | 105588884 |
| 50001397 | 2021-09-19 03:33:07 |    106198652 |                0 | NULL    | 2021-09-19 03:33:07 | 125715813 |
| 50001398 | 2021-09-19 03:33:07 |    125715813 |                0 | NULL    | 2021-09-19 03:33:07 | 106198652 |

问题 2# 我正在尝试使用以下命令添加新列

ALTER TABLE Friends ADD COLUMN source INT(11) DEFAULT '0';

但是在表更改时低于错误

ERROR 1062 (23000): Duplicate entry '126750770-127846472' for key 'userId'

更新 -

选择上述键的结果

mysql> select * from Friends where userId = 126750770 AND friendUserId = 127846472;
+------------+---------------------+--------------+------------------+---------+---------------------+-----------+--------+
| id         | createdTime         | friendUserId | friendshipStatus | remarks | updatedTime         | userId    | source |
+------------+---------------------+--------------+------------------+---------+---------------------+-----------+--------+
| 1120753287 | 2022-01-27 12:36:12 |    127846472 |                0 | NULL    | 2022-01-27 12:36:12 | 126750770 |      0 |
+------------+---------------------+--------------+------------------+---------+---------------------+-----------+--------+
1 row in set (0.00 sec)

mysql> select * from Friends where userId = 127846472 AND friendUserId = 126750770;
+------------+---------------------+--------------+------------------+---------+---------------------+-----------+--------+
| id         | createdTime         | friendUserId | friendshipStatus | remarks | updatedTime         | userId    | source |
+------------+---------------------+--------------+------------------+---------+---------------------+-----------+--------+
| 1120753288 | 2022-01-27 12:36:12 |    126750770 |                0 | NULL    | 2022-01-27 12:36:12 | 127846472 |      0 |
+------------+---------------------+--------------+------------------+---------+---------------------+-----------+--------+
1 row in set (0.00 sec)
mysql alter-table