Geek asks and answers

Primary Key ID Reaching Bigint Data Type Limit

I have a table that undergoes large inserts and deletes on a regular basis (and because of this there are large spaces in the numerical sequence of the primary identifier column). He had a primary identifier column of type "int", which was changed to "bigint". Despite this change, the limit of this data type will also inevitably be exceeded at some point in the future (current usage will indicate that this will take place over the next year or so).

How do you deal with this scenario? I am wondering (terrible horror) if I even need a primary key column, since it is not used in any way in any queries or is not mentioned by other tables, etc. Will the column remove the solution? Or can this kind of action drop you from the mysql community?

We are almost at 500 million for the auto-increment identifier. The table stores keywords related to file data in a separate table. Each line of file data can contain up to 30 keywords in the keyword table, so they really begin to add up after you insert and delete tens of thousands of files. Currently, the keyword table contains the keyword and the identifier of the file associated with it, so if I got rid of the current column of the primary identifier, there would be no unique identifier other than the fields of the keyword (varchar) and file id (int), which would be a terrible primary key.

All thoughts / answers / experience / solutions are very grateful.

+5
source share
4

, . _ : measure_id + location_id, . , , .

, ?

, ?

+9

, , ,

500 , BIG INT 1173 . , ,

+21

(100 000) , 2 924 712

1 (1 000 000) , 292 471

10 (10 000 000) , 29 247

100 , 2,925

1000 , 292

+2

, , DELETE,

SQL SERVER

CREATE TRIGGER resetidentity
    ON dbo.[table_name]
    FOR DELETE
AS
    DECLARE @MaxID INT
    SELECT @MaxID = ISNULL(MAX(ID),0)
    FROM dbo.[table_name]
    DBCC CHECKIDENT('table_name', RESEED, @MaxID)
GO

In a nutshell, this will reset your identifier (in the case of auto-increment and primary). Example: if you have 800 lines and 400 of them are deleted, then the next time you insert it, it will start from 401 instead of 801.

But down this will not change your identifier if you delete it on the middle record. If you have 800 lines and the ID 200-400 is deleted, the identifier will still be considered at 801 the next time you write new lines

0
source

More articles:


All Articles