I want to create a column with "insertable indexed key", by that, I mean

1. I can insert some random data between value A and the value next to A, like 20 -> (insert data) -> 21

2. I can use the key(probably unique key) to operate on data, like getByKey, deleteByKey, etc.

3. I can compare between keys, like I will know, something between 20 and 30 is greater than 20 and less than 30(values don`t have to be numeral, comparison don`t have to be numeric comparison)

what I have tried - with primary key auto_increment

apparently I can not use auto_increment pk in this case, I can not insert between id=20 and id=21

what I have tried - with varchars

Probably I can use varchars, for example:

# column type varchar(10) -  original data
"20"
"21"

# insert 2 item between 20 and 21

"20"
"201"  <- insert first
"202"  <- insert second
"21"

# insert 1 item between 201 and 202

"20"
"201"
"2011" <- insert
"202"
"21"

The problem here is that

1. I have to maintain whole generating algorithm by myself, which is very complicated. I have to check whether there is a value on the same spot, or do a few comparisons and have to decide whether to expand the digits or not
2. If I manage to solve problem 1, I have to add thread-safe code, which is much more complicated.

what I have tried - with multiple column

I don`t think multiple columns are solutions here, I want to keep it simple. But if there are solutions that uses multiple columns, I really want to hear it.

question

can you give a solution for this? Any suggestions are appreciated.

CodePudding user response：

You are describing what every BASIC programmer has dealt with since 1964. :-)

You could use a FLOAT or DOUBLE column as the primary key, so you could keep inserting fractional values between the existing primary key values. There's a limit to the precision, given that these data types are of fixed size, but it's not clear from your question how frequently you would have to insert a new row between values.

create table mytable ( id float primary key );

insert into mytable (id) values (20), (21);

insert into mytable (id) values (20.5);

insert into mytable (id) values (20.75);

select * from mytable;
 ------- 
| id    |
 ------- 
|    20 |
|  20.5 |
| 20.75 |
|    21 |
 ------- 

This also would require you to manage the id generation yourself, which you said you find inconvenient.

And as you said, making it thread-safe would require you to lock ranges of rows, so you could prevent race conditions.

I don't think there is any solution to do what you want that works as automatically as an auto-increment integer. You're going to have to implement some code.