mysql将多个数据插入单个列或多个行

Question

just want to ask for an opinion regarding mysql. which one is the better solution?

case1: store in 1 row:-

product_id:1

attribute_id:1,2,3

when I retreive out the data, I split the string by ','

I saw some database, the store the data in this way, the record is a product, the column is stored product attribute:

a:3:{s:4:"spec";a:2:{i:1;s:6:"black";i:3;s:2:"37";}s:21:"spec_private_value_id";a:2:{i:1;s:11:"12367591683";i:3;s:11:"12367591764";}s:13:"spec_value_id";a:2:{i:1;s:1:"5";i:3;s:2:"29";}}

or

case2: store in 3 row:-

product_id:1

attribute_id:1

---

product_id:1

attribute_id:2

---

product_id:1

attribute_id:3

---

this is the normal I do, to store 3 rows for the attribute for a record.

In term of performance and space, anyone can tell me which one is better. From what I see is case1 save space, but need to process the data in PHP (or other server side scripting). case2 is more straight forward, but use spaces.

Answer 1

Save space? Seriously? You're talking about saving bytes when a one terabyte disk goes for 70 dollars?

And maybe you're not even saving bytes. If you store attributes as "12234,23342,243234", that's like 30 bytes for 3 attributes. If you'd store them as smallint, they'd take up 6 bytes.