CODEWITHSUNDEEP
mysql
Aditi
Aditi

Reputation: 111

Can we store List type of data to MYSQL database table?

I need to store some data of list type into a MYSQL database.Is it possible to store it in MYSQL,if yes then what should be the data type of the field that will hold this data?

Thanks in advance.

Upvotes: 9

Views: 36736

Answers (5)

Robert Johnson
Robert Johnson

Reputation: 11

You could create a table with two fields - the first being the list name and the second being the list items. This table would be like a join between the list and its items. Each list name would appear in the table as many times as there are items in the list. Each list item would appear in the table as many times as the number of lists it belongs to.

For example, say you want to store data about teams. Let's say that any person can be a member of more than one team. If Bob is on Team Blue and on Team Orange then there would be two records that include Bob - one where he's paired with Team Blue and one where he's paired with Team Orange. And if there are 10 people on Team Blue then there would be 10 records where Team Blue is mentioned - one for each member of Team Blue.

I think this solution does not follow the rules of normalization but it would be a practical, workable solution, wouldn't it?

Upvotes: 0

Aylian Craspa
Aylian Craspa

Reputation: 466

you can use SET datatype and this will store predefined data set in the mysql and not in the table and let you select value from the list up to 64 individual items

https://dev.mysql.com/doc/refman/8.0/en/set.html

Upvotes: 0

juergen d
juergen d

Reputation: 204756

No, no, no, no, no! Never store multiple values in a single column!

Seperate the values and store each of them in another record.

For instance if you have users and want to store a list of the roles each user has then you can do it like this

users table
-----------
id
name
...


roles table
-----------
id
name


user_roles table
----------------
user_id
role_id

Upvotes: 13

digitai
digitai

Reputation: 1842

You can store it as a STRING, using VARCHAR data type column.

There is no specific data type in mysql designed to store specifically lists.

Other approach is to implode the list and store each member in a different row, then when queried, you can recreate the list, or array or JSON, in your preferred language.

Upvotes: 3

Wajih
Wajih

Reputation: 4383

You can use JSON datatype. MySQL provides JSON as column data type and provides some functions to work with JSON data.

Look at the documentation

NOTE: you must use 5.7+ version

As of MySQL 5.7.8, MySQL supports a native JSON data type that enables efficient access to data in JSON

Upvotes: 5

Related Questions