mysql 在字段中的重复值上，插入具有新值的新行 [英] mysql On Duplicate value in field, insert new row with new value
问题描述
如果在唯一字段中输入重复值，我想在表中添加一条新记录.我不想更新现有记录，但想通过修改唯一字段值来添加新记录.这在mysql中可能吗?
在用户评论此帖子后
您需要在这两个进程上写表锁定.>
WRITE 锁具有以下特点:
唯一持有表锁的会话可以从表中读写数据.
在释放 WRITE 锁之前，其他会话无法从表中读取数据和向表中写入数据.
编辑前:
是的，这是可能的.我花了一段时间才弄明白.我将它建立在您的输入和比较值上，如 test1、test2 等，其中 test 始终相同并具有尾随编号.正如你所指定的.
它可以通过 MySQL TRANSACTION 分 4 步完成.
假设您有一个表 testT，其中名称是唯一的，以确保我们没有双打.
<代码>|身份证 |姓名 ||--- |----- ||1 |测试1 ||2 |测试3 |
并且您想插入一个名为 test1 的新项目，我们设置为:
SET @newName = 'test1';然后我们需要检查它是否已经存在于表中:
SELECT @check:=COUNT(*) FROM testT WHERE name = @newName;我们在这里进行计数以获取真假并在此处将其保存为 @check 以便我们稍后进行比较.这将导致 1 行，因为 test1 已经存在于表中.
接下来我们做另一个选择以获得最高的测试数量*并将其存储为@number，下一个查询选择所有测试并在4之后执行SUBSTRING，后者为我们提供前4之后的所有数字后者.(99999999999) 数字实际上只是为了确保我们不会错过任何数字，但在我们的情况下，结果仅为3"，因为这是表中的最后一条记录test3".
SELECT@number:= SUBSTRING(name,5,99999999999)从 testT;现在我们可以做一个插入:
INSERT INTO testT(name)价值观(IF(@check = "", @newName , CONCAT(LEFT(@newName,4),RIGHT(@number,1)+1)));这会尝试在 IF 条件下将我们的 @newName 插入表中，即如果我们的 @check 为空，则他将插入 @newName，如果不是，它将从字符串中取出单词 test 并从前面附加一个最高的 @number 并添加 + 1 .
@newName = 'test1' 的结果如下.如果将其更改为 @newName = 'test3' 结果将是相同的新插入 test4.
**Schema (MySQL v5.7)**SET @newName = 'test1';---**查询#1**SELECT * FROM testT按 id 排序；|身份证 |姓名 ||--- |----- ||1 |测试1 ||2 |测试3 ||3 |测试4 |---如果您在任何测试中更改它*该号码尚不存在，它将正常插入.在以下情况下:@newName = 'test6'
SET @newName = 'test6';**查询#1**SELECT * FROM testT按 id 排序；|身份证 |姓名 ||--- |----- ||1 |测试1 ||2 |测试3 ||3 |测试6 |这种方式将始终插入.
你可以在这里玩这个:查看 DB Fiddle改变 SET @newName = 'test6'
我不是专家，我花了几个小时才弄明白这个方法，因为我想知道这是否可行.如果任何其他用户可以建议任何其他方式或改进我的方法，我将不胜感激.
I want to add a new record in a table if duplicate value enters in a unique field. I don't want to update the existing one but want to add a new record by modifying the unique field value. Is this possible in mysql?
EDIT:
Edited after user comment on this post:
You need write table locking on both of those two processes.
A WRITE lock has the following features:
The only session that holds the lock of a table can read and write data from the table.
Other sessions cannot read data from and write data to the table until the WRITE lock is released.
Also look at SQL UNIQUE Constraint
BEFORE EDIT:
Yes it is possible. And it took me awhile to figure it out. I build this on your input and compering values as test1, test2 etc, where test is always the same and has trailing number. As you specified.
It can be done as MySQL TRANSACTION in 4 steps.
Lets say you have table testT where name is unique to insure we have no doubles.
| id | name |
| --- | ----- |
| 1 | test1 |
| 2 | test3 |
And you want to insert a new item with name test1 we set is as:
SET @newName = 'test1';
Then we need to check if it already exists in table:
SELECT @check:=COUNT(*) FROM testT WHERE name = @newName;
We do a count here to get true or false and save it as @check here so we can compare it later. This will result into 1 row as test1 already exists in table.
Next we do another selection to get the highest number of test* and store it as @number, this next query selects all tests and does a SUBSTRING after 4 latter's giving us all numbers after first 4 latter's. (99999999999) numbers actually just to be sure we don't miss any but in our case result is only "3" because that is last record "test3" in table.
SELECT
@number:= SUBSTRING(name,5,99999999999)
FROM testT;
Now we can do an insert:
INSERT INTO testT(name)
VALUES
(
IF(@check = "", @newName , CONCAT(LEFT(@newName,4),RIGHT(@number,1)+1)
)
);
This tries to insert our @newName into table under IF condition, and that is if our @check is empty then he will insert @newName, if not it will take word test out of string and append a highest @number from earlier and add + 1 too it.
So result for @newName = 'test1' is below. If you change this into @newName = 'test3' result wold be same new insert test4.
**Schema (MySQL v5.7)**
SET @newName = 'test1';
---
**Query #1**
SELECT * FROM testT
ORDER BY id;
| id | name |
| --- | ----- |
| 1 | test1 |
| 2 | test3 |
| 3 | test4 |
---
And if you change it in ANY test* that number does not already exists it will insert it normally. In case below: @newName = 'test6'
SET @newName = 'test6';
**Query #1**
SELECT * FROM testT
ORDER BY id;
| id | name |
| --- | ----- |
| 1 | test1 |
| 2 | test3 |
| 3 | test6 |
This way an insert will always be made.
You can play with this here : View on DB Fiddle just by changing SET @newName = 'test6'
I am no expert and it took me couple of hours to figure this way out, as I wanted to know if this was even possible. And I would appreciate if any other user can suggestion any other way or improve my method.
这篇关于mysql 在字段中的重复值上，插入具有新值的新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
