In Table MySQL, attach the text field - the numerical identifier



  • How's mySQL using a text field with a type of car, a numerical identifier? For example, there is a table:

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

    We need to make Sedan have an identifier, one, the SUV 2 etc. To succeed:

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


  • Let's say 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 check on the test table first, or if there's not much mistake




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2