Mysql - auto increment shared between tables

Question

It is possible to share the auto increment value of two tables?

Example:

Tabela 1
+----+-------+-----------+
| id | campo | criado_em |
| 1  |   a   |   00:00   |
| 2  |   b   |   01:00   |
| 5  |   c   |   04:00   |
+----+-------+-----------+

Tabela 2
+----+-------+-----------+
| id | campo | criado_em |
| 3  |   a   |   02:00   |
| 4  |   b   |   03:00   |
| 6  |   c   |   05:00   |
+----+-------+-----------+

Answer 1

In my sql do not know, you can try to create a Quence, I do not know if it would work, in Oracle I would create a Quence, only that at the time of giving Insert you will have to give a nome_da_sequence.nextval

 create sequence idcodcli

increment by 1

start with 10

maxvalue 999

nocycle;

Select :

select sequence_name,increment_by,max_value, last_number
           from user_sequences;

Answer 2

I point you to use a link table and put the AUTO_INCREMENT in this table with foreign key for the other two. In each of the main tables you would create a TRIGGER which would fill the link table at the time of insertion. Then just select the data as described in query down below:

Schema (Mysql v5.7)

CREATE TABLE tabela1 (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  campo VARCHAR(3)
);

CREATE TRIGGER trg_i_tabela1
AFTER INSERT ON tabela1
FOR EACH ROW
  INSERT INTO vinculo(id_tabela1) VALUES(NEW.id);

CREATE TABLE tabela2 (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  campo VARCHAR(3)
);

CREATE TRIGGER trg_i_tabela2
AFTER INSERT ON tabela2
FOR EACH ROW
  INSERT INTO vinculo(id_tabela2) VALUES(NEW.id);

CREATE TABLE vinculo (
  id         INTEGER AUTO_INCREMENT PRIMARY KEY,
  id_tabela1 INTEGER,
  id_tabela2 INTEGER,
  FOREIGN KEY fk_vinculo_tabela1(id_tabela1) REFERENCES tabela1(id),
  FOREIGN KEY fk_vinculo_tabela2(id_tabela2) REFERENCES tabela2(id)
);

INSERT INTO tabela1(campo) VALUES('a1');
INSERT INTO tabela1(campo) VALUES('b1');
INSERT INTO tabela2(campo) VALUES('a2');
INSERT INTO tabela2(campo) VALUES('b2');
INSERT INTO tabela1(campo) VALUES('c1');
INSERT INTO tabela2(campo) VALUES('c2');

---

Query

SELECT v.id,
       COALESCE(t1.campo, t2.campo) AS campo
  FROM vinculo v
  LEFT JOIN tabela1 t1 ON t1.id = v.id_tabela1
  LEFT JOIN tabela2 t2 ON t2.id = v.id_tabela2
 ORDER BY v.id;

---

Upshot

| id  | campo |
| --- | ----- |
| 1   | a1    |
| 2   | b1    |
| 3   | a2    |
| 4   | b2    |
| 5   | c1    |
| 6   | c2    |

---

See working on DB Fiddle.