CODEWITHSUNDEEP
mysql
Jothi
Jothi

Reputation: 15090

dynamically specify data type for a column in mysql?

i have one problem that, i have to change type of data in column based on foreign key stored in that row. 

unit_id   unit_name
1          String
2          Float
3          Date
4          Int


Id      spec value   unit id
1         "A"         1
2         30.90       2
3         null        3
4         100         4

now i should achieve the above condition. how do i achieve it?

Upvotes: 1

Views: 96

Answers (1)

Chris Lercher
Chris Lercher

Reputation: 37778

This is not possible. What you can do:

  • Use VARCHAR (or something similar) for spec value, and parse the content yourself.
  • Have several columns (spec value_string, spec value_float, ...) Problem: The table will contain lots of null values. But this approach can often be seen for reporting tables.
  • Have several tables, one for strings, one for floats, and join them with the datatype table depending on unit_name.

Upvotes: 1

Related Questions