Store a variable-sized list of strings in some column

Question

I am a newbie in sql and mysql (in fact, I use mariadb implementation). I need to store a variable-sized list of strings in some column. Example: it may take the value

'one' or ['one', 'two'], or ['one', 'two', 'three'] just like an array type in python or other high level languages.

What kind of data-type (if such exists) can I use?

Have you tried Googling this? These answers should help (the bottom line, though, is you're best using another table): stackoverflow.com/questions/6360739/… stackoverflow.com/questions/5341149/… — Philip Stratford
– Philip Stratford, Commented Jun 2, 2016 at 22:59

Bill · Accepted Answer · 2016-06-03 15:08:32Z

-1

There isn't a data-type to do what you are wanting to do directly. Rather you would store your "variable-sized list of strings" in another table, and use a column from your existing table to refer to that other table. This is called a join.

For example, if you have a table structured like so...

TABLE: Shelf
id : int
location : varchar(50)
books : int

And another table

TABLE: Books
bookId: int
Title: varchar(80)

Then you could find all the books at location x with the following query...

SELECT s.location, b.title FROM Shelf s
INNER JOIN Books b on s.books = b.bookId
WHERE s.location = 'x'
ORDER BY b.title;

There is a far better explanation of this at https://www.sitepoint.com/understanding-sql-joins-mysql-database/

edited Jun 3, 2016 at 15:08

answered Jun 2, 2016 at 23:06

Bill

2,6411 gold badge27 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ejonas GGgg Over a year ago

Thank you! However, I am not sure this is what I have to do. That means I should create a table with some unique name that contains row of CHAR entries. Isn't that a lot of manual work? And How should access a table with a variable name (the name listed in the base table)?

Bill Over a year ago

A lot of manual work is a subjective term. For a database such as MySql, joins to other tables are how you handle multivalue data. If you really need to collapse everything into one column, you'll need to find a different type of database. Multivalue databases that .come to mind are U2 (unidata) or OpenQm, Here are some references openqm-zumasys.com/openqm/what-is-multivalue and nosql-database.org

Collectives™ on Stack Overflow

Store a variable-sized list of strings in some column

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related