[mysql] How can I simulate an array variable in MySQL?

Is an array variable really necessary?

I ask because I originally landed here wanting to add an array as a MySQL table variable. I was relatively new to database design and trying to think of how I'd do it in a typical programming language fashion.

But databases are different. I thought I wanted an array as a variable, but it turns out that's just not a common MySQL database practice.

Standard Practice

The alternative solution to arrays is to add an additional table, and then reference your original table with a foreign key.

As an example, let's imagine an application that keeps track of all the items every person in a household wants to buy at the store.

The commands for creating the table I originally envisioned would have looked something like this:

#doesn't work
CREATE TABLE Person(
  name VARCHAR(50) PRIMARY KEY
  buy_list ARRAY
);

I think I envisioned buy_list to be a comma-separated string of items or something like that.

But MySQL doesn't have an array type field, so I really needed something like this:

CREATE TABLE Person(
  name VARCHAR(50) PRIMARY KEY
);
CREATE TABLE BuyList(
  person VARCHAR(50),
  item VARCHAR(50),
  PRIMARY KEY (person, item),
  CONSTRAINT fk_person FOREIGN KEY (person) REFERENCES Person(name)
);

Here we define a constraint named fk_person. It says that the 'person' field in BuyList is a foreign key. In other words, it's a primary key in another table, specifically the 'name' field in the Person table, which is what REFERENCES denotes.

We also defined the combination of person and item to be the primary key, but technically that's not necessary.

Finally, if you want to get all the items on a person's list, you can run this query:

SELECT item FROM BuyList WHERE person='John';

This gives you all the items on John's list. No arrays necessary!

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to arrays

PHP array value passes to next row Use NSInteger as array index How do I show a message in the foreach loop? Objects are not valid as a React child. If you meant to render a collection of children, use an array instead Iterating over arrays in Python 3 Best way to "push" into C# array Sort Array of object by object field in Angular 6 Checking for duplicate strings in JavaScript array what does numpy ndarray shape do? How to round a numpy array?

Examples related to variables

When to create variables (memory management) How to print a Groovy variable in Jenkins? What does ${} (dollar sign and curly braces) mean in a string in Javascript? How to access global variables How to initialize a variable of date type in java? How to define a variable in a Dockerfile? Why does foo = filter(...) return a <filter object>, not a list? How can I pass variable to ansible playbook in the command line? How do I use this JavaScript variable in HTML? Static vs class functions/variables in Swift classes?

Examples related to set

java, get set methods golang why don't we have a set datastructure Simplest way to merge ES6 Maps/Sets? Swift Set to Array JavaScript Array to Set How to sort a HashSet? Python Set Comprehension How to get first item from a java.util.Set? Getting the difference between two sets Python convert set to string and vice versa

Examples related to temp-tables

How to find difference between two columns data? Temporary table in SQL server causing ' There is already an object named' error SQL Server Creating a temp table for this query Create a temporary table in MySQL with an index from a select How can I simulate an array variable in MySQL? When should I use a table variable vs temporary table in sql server? Inserting data into a temporary table TSQL select into Temp table from dynamic sql dropping a global temporary table Is there a way to get a list of all current temporary tables in SQL Server?