How in MySQL to assign a numeric identifier to a text field with a car type? For example, there is a table:

+------------+------------------- | id_model | Type | +------------+------------------- | 12 | Седан | | 13 | Внедорожник | | 14 | Универсал | | 15 | Купе | | ...... | ...... | | 2032 | Седан | +------------+-------------------

It is necessary to make the Sedan have the identifier - 1, the SUV - 2, etc. To get it:

 +------------+------------------- | id_model | Type | +------------+------------------- | 12 | 1 | | 13 | 2 | | 14 | 3 | | 15 | 4 | | ...... | ...... | | 2032 | 1 | +------------+-------------------

    1 answer 1

    Suppose your car table is called cars

     CREATE TABLE types( id INT NOT NULL AUTO_INCREMENT, type VARCHAR(20), PRIMARY KEY(id) ); INSERT INTO types (type) SELECT type FROM cars; ALTER TABLE cars CHANGE COLUMN type type_old VARCHAR(20); ALTER TABLE cars ADD type INT; UPDATE cars t1, types t2 SET t1.type=t2.id WHERE t2.type=t1.type_old; ALTER TABLE cars DROP COLUMN type_old; ALTER TABLE cars ADD FOREIGN KEY (type) REFERENCES types(id);

    PS Only on the test table, first check, and then I did it a little where