当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - Insert only when auto-increment id is not equal 6(for example)?

问题描述:

I have a table with 3 fields: Id(PK,AI), Name(varchar(36)), LName(varchar(36)).

I have to insert name and last name, Id inserts automatically because of it's constraints,

Is There a way to Jump id auto increment value when it reaches 6?

for instance do this 7 times:

Insert Into table(Name, LName) Values ('name1', 'lname1') "And jump id to 7 if it is going to be 6"

It may sound stupid to do this but I have the doubt.

Also Jump and do not record id 6.

record only, 1-5, 7,8,9 and so on

What I want to achieve starts from a Union:

Select * From TableNames

Union All

Select * From TableNames_general

In the TableNames_general I assign it's first value so that when the user sees the table for the first time it will be displayed the record I inserted.

The problem comes when the user inserts a new record, if the Id of the inserted record is the same as the one I have inserted it will be duplicated, that is why I want to achieve when the users inserts one record and if the last insert id already exists just jump that record. this is because I must have different ids due to its relationship among child tables.

网友答案:

Identity column generate values for you, And its best left this way, You have the ability to insert specific values in Identity column but its best left alone and let it generate values for you.

Imagine you have inserted a value explicitly in an identity column and then later on Identity column generates the same value for you, you will end up with duplicates.

If you want to have your input in that column then why bother with identity column anyway ??

Well this is not the best practice but you can jump to a specific number by doing as follows:

MS SQL SERVER 2005 and Later

-- Create test table
CREATE TABLE ID_TEST(ID INT IDENTITY(1,1), VALUE INT)
GO

-- Insert values
INSERT INTO ID_TEST (VALUE) VALUES
(1),(2),(3)
GO

-- Set idnentity insert on to insert values explicitly in identity column
SET IDENTITY_INSERT ID_TEST ON;

INSERT INTO ID_TEST (ID, VALUE) VALUES
(6, 6),(8,8),(9,9)
GO

-- Set identity insert off
SET IDENTITY_INSERT ID_TEST OFF;
GO

-- 1st reseed the value of identity column to any smallest value in your table
-- below I reseeded it to 0
DBCC CHECKIDENT ('ID_TEST', RESEED, 0);
-- execute the same commad without any seed value it will reset it to the 
-- next highest idnetity value
DBCC CHECKIDENT ('ID_TEST', RESEED);
GO

-- final insert 
INSERT INTO ID_TEST (VALUE) VALUES
(10)
GO

-- now select data from table and see the gap
SELECT * FROM ID_TEST
网友答案:

If you query the database to get the last inserted ID, then you can check if you need to increment it, by using a parameter in the query to set the correct ID.

网友答案:

If you use MSSQL, you can do the following:

Before you insert check for the current ID, if it's 5, then do the following:

  • Set IDENTITY_INSERT to ON
  • Insert your data with ID = 7
  • Set IDENTITY_INSERT to OFF

Also you might get away with the following scenario:

  • check for current ID
  • if it's 5, run DBCC CHECKIDENT (Table, reseed, 6), it will reseed the table and in this case your next identity will be 7

If you're checking for current identity just after INSERT, you can use SELECT @@IDENTITY or SELECT SCOPE_IDENTITY() for better results (as rcdmk pointed out in comments)

Otherwise you can just use select: SELECT MAX(Id) FROM Table

网友答案:

There's no direct way to influence the AUTO_INCREMENT to "skip" a particular value, or values on a particular condition.

I think you'd have to handle this in an AFTER INSERT trigger. An AFTER INSERT trigger can't update the values of the row that was just inserted, and I don't think it can make any modifications to the table affected by the statement that fired the trigger.

A BEFORE INSERT trigger won't work either, because the value assigned to an AUTO_INCREMENT column is not available in a BEFORE INSERT trigger.


I don't believe there's a way to get SQL Server IDENTITY to "skip" a particular value either.


UPDATE

If you need "unique" id values between two tables, there's a rather ugly workaround with MySQL: roll your own auto_increment behavior using triggers and a separate table. Rather than defining your tables with AUTO_INCREMENT attribute, use a BEFORE INSERT trigger to obtain a value.

If an id value is supplied, and it's larger than the current maximum value from the auto_increment column in the dummy auto_increment_seq table, we'd need to either update that row, or insert a new one.

As a rough outline:

CREATE TABLE auto_increment_seq 
(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT) ENGINE=MyISAM;

DELIMITER $$

CREATE TRIGGER TableNames_bi
BEFORE INSERT ON TableNames
FOR EACH ROW
BEGIN
   DECLARE li_new_id INT UNSIGNED;
   IF ( NEW.id = 0 OR NEW.id IS NULL ) THEN
      INSERT INTO auto_increment_seq (id) VALUES (NULL);
      SELECT LAST_INSERT_ID() INTO li_new_id;
      SET NEW.id = li_new_id;
   ELSE
      SELECT MAX(id) INTO li_max_seq FROM auto_increment_seq;
      IF ( NEW.id > li_max_seq ) THEN
         INSERT INTO auto_increment_seq (id) VALUES (NEW.id);
      END IF;
   END IF;
END$$

CREATE TRIGGER TableNames_ai
AFTER INSERT ON TableNames
FOR EACH ROW BEGIN
   DECLARE li_max_seq INT UNSIGNED;
   SELECT MAX(id) INTO li_max_seq FROM auto_increment_seq;
   IF ( NEW.id > li_max_seq ) THEN
      INSERT INTO auto_increment_seq (id) VALUES (NEW.id);
   END IF;
END;

DELIMITER ;

The id column in the table could be defined something like this:

TableNames
( id INT UNSIGNED NOT NULL DEFAULT 0 PRIMARY KEY 
    COMMENT 'populated from auto_increment_seq.id'
, ...

You could create an identical trigger for the other table as well, so the two tables are effectively sharing the same auto_increment sequence. (With less efficiency and concurrency than an Oracle SEQUENCE object would provide.)

IMPORTANT NOTES

This doesn't really insure that the id values between the tables are actually kept unique. That would really require a query of the other table to see if the id value exists or not; and if running with InnoDB engine, in the context of some transaction isolation levels, we might be querying a stale (as in, consistent from the point in time at the start of the transaction) version of the other table.

And absent some additional (concurrency killing) locking, the approach outline above is subject to a small window of opportunity for a "race" condition with concurrent inserts... the SELECT MAX() from the dummy seq table, followed by the INSERT, allows a small window for another transaction to also run a SELECT MAX(), and return the same value. The best we can hope for (I think) is for an error to be thrown due to a duplicate key exception.

This approach requires the dummy "seq" table to use the MyISAM engine, so we can get an Oracle-like AUTONOMOUS TRANSACTION behavior; if inserts to the real tables are performed in the context of a REPEATABLE READ or SERIALIZABLE transaction isolation level, reads of the MAX(id) from the seq table would be consistent from the snapshot at the beginning of the transaction, we wouldn't get the newly inserted (or updated) values.

We'd also really need to consider the edge case of an UPDATE of row changing the id value; to handle that case, we'd need BEFORE/AFTER UPDATE triggers as well.

分享给朋友:
您可能感兴趣的文章:
随机阅读: