mysql eav,MySQL：如何转换为EAV？

Say I have the following table:

TABLE: one

===============================

| id | first_name | last_name |

===============================

| 1 | John | Doe |

| 2 | Jane | Smith |

-------------------------------

I want to convert it to an EAV:

TABLE: two

===================================

| id | fk_id | attribute | value |

===================================

| 1 | 1 | first_name | John |

| 2 | 1 | last_name | Doe |

| 3 | 2 | first_name | Jane |

| 4 | 2 | last_name | Smith |

-----------------------------------

Note:

two.fk_id values came from one.id

two.attribute values came from one.first_name

two.value values came from one.last_name

I need to create table two from table one so I can run some tests.

解决方案

I'm assuming two.id is an autoincrementing integer, so I'll leave it to the target table to assign those values.

select id as fk_id, 'first_name' as attribute, first_name as value

from one

union all

select id as fk_id, 'last_name', last_name

from one

order by fk_id, attribute