mysqltriggersinsert-statement

Can I have an MySQL database auto-assign a value to a column after an insert statement?


Given a table employee with columns ID(pk) and name

ID|name
--+-----
1 |John
2 |James
3 |Tom

Can I do

INSERT INTO employee (name) VALUES (Jack);

and somehow have the database auto assign the next available ID? Can a trigger do it? If so, how?


Solution

  • Create your table with an identity autoincrement column:

    CREATE TABLE table_name
    (
       id INTEGER AUTO_INCREMENT PRIMARY KEY,
       -- other columns to follow
    )