0

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?

Tony Babarino
  • 3,355
  • 4
  • 32
  • 44
Ejonas GGgg
  • 446
  • 1
  • 6
  • 19
  • Have you tried Googling this? These answers should help (the bottom line, though, is you're best using another table): http://stackoverflow.com/questions/6360739/how-to-store-array-or-multiple-values-in-one-column http://stackoverflow.com/questions/5341149/store-array-of-numbers-in-database-field – Philip Stratford Jun 02 '16 at 22:59

1 Answers1

0

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/

Bill
  • 2,623
  • 1
  • 27
  • 41
  • 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)? – Ejonas GGgg Jun 02 '16 at 23:25
  • 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 http://www.openqm-zumasys.com/openqm/what-is-multivalue/ and http://nosql-database.org/ – Bill Jun 03 '16 at 15:13