PHP-SQL自动添加增量数据

Question

Hi I want to create a php-sql application for my case study where in I have these attributes:

Book ID, Book Name, Author, Editor, ISBN

the user will input the Book Name, Book ID, Author and Editor and automatically there will be an input for ISBN Number that is increments every time the user will input. Is that possible anyway? Well thanks for the help in advance.

Answer 1

It's unclear why you need this but you can achieve your goal with a BEFORE INSERT trigger and a separate table for sequencing

Tables

-- A sequencing table
CREATE TABLE book_seq 
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);
-- Set initial value
ALTER TABLE book_seq AUTO_INCREMENT = 9000;

CREATE TABLE books
(
  book_id   INT,
  book_name VARCHAR(256), 
  author    VARCHAR(256),
  editor    VARCHAR(256),
  isbn      VARCHAR(12)
);

A trigger

DELIMITER $$
CREATE TRIGGER tg_book_seq
BEFORE INSERT ON books
FOR EACH ROW
BEGIN
  INSERT INTO book_seq VALUES(NULL);
  SET NEW.isbn = CONCAT('8000-', LAST_INSERT_ID());
END$$
DELIMITER ;

Now you can simply insert rows

INSERT INTO books (book_id, book_name, author, editor) VALUES
(1, 'Book1 Title', 'Author1', 'Editor1'),
(2, 'Book2 Title', 'Author2', 'Editor1');

Your pseudo ISBN number will be auto-generated

| BOOK_ID |   BOOK_NAME |  AUTHOR |  EDITOR |      ISBN |
---------------------------------------------------------
|       1 | Book1 Title | Author1 | Editor1 | 8000-9000 |
|       2 | Book2 Title | Author2 | Editor1 | 8000-9001 |

Here is SQLFiddle demo

---

Another, much simpler, approach is to use INSERT ... SELECT syntax and calculate next pseudo ISBN number at the time of insert with the query like this

INSERT INTO books (book_id, book_name, author, editor, isbn) 
SELECT 1, 'Book1 Title', 'Author1', 'Editor1',
       CONCAT('8000-', COALESCE(SUBSTRING_INDEX(MAX(isbn), '-', -1), '8999') + 1) new_value
  FROM books

Here is SQLFiddle demo

Note: the major drawback of this approach is that it might fail under heavy load in concurrent environment when two or more concurrent users may have get the same MAX(isbn) value thus producing rows with the duplicate isbn number.