mysql - 怎样為一個table加一個auto_increment的field而不是primary key

Question

我有一個table,field 有三個. id, name , sort {代码...} sort的作用只是用來記錄次序. 而id已經設定auto_increment. 當我新增記錄, sort的數值是未知. 請問可否設定新增記錄時, sort會同id一致. 比如: {代码...}

阿神 · Answer

1. Get the current maximum id, max(id)+1

INSERT INTO myTable (name,sort) 
  SELECT 'Ali' as name,max(id)+1 as sort FROM myTable;

2. Trigger
CREATE TRIGGER t_afterinsert_on_myTable
AFTER INSERT ON myTable
FOR EACH ROW
BEGIN

 update myTable set sort=id where id=new.id;

END;
Then when inserting, just insert the name value, INSERT INTO myTable (name) VALUES ('Ali');

Of course the first one is simpler. . .

Php8, I'm coming too