Data type of array in mysql

Question

I have a variable of type array and I want to store it as it is in database(mysql) and when I apply tinyblob that I usually use in java for display it return abnormal output. So what is the correct datatype for the array in php.

$countryGoup=array("USA","FRANCE","GERMANY","BRITAIN");

Answer 1

You could:

1) use a SET datatype if the values are few and fixed

You define the column in the CREATE TABLE statement as SET('USA', 'France', 'Germany', ...) NOT NULL (not null optional), and use queries like:

INSERT INTO myTable SET countries="USA,Germany";

---

2) use a lookup table with a foreign reference

So you define a countries table:

id_country|name
----------+-----------
1         |USA
2         |Germany
3         |France

and define a pivot table that links your main object (f.ex. "users") to the countries, thus esabilishing a many-to-many relationship.

F.ex., a users_countries table would link users table to countries table in this way:

id_user|id_country
------------------
1      |1
1      |2
2      |1

In this example, user 1 is linked to countries with id 1 and 2 (USA and Germany), user 2 is linked to USA only, and so on

---

3) use a comma-separated string, or other separators, or serialize the array

This involves using the server-side language (in your case PHP) to concat the values with some separator (such as comma , or pipe |):

$countries = array("USA", "Russia", "Iran");
$countriesString = implode (",", $countries); // $countriesString = "Usa,Russia,Iran"
$query = "INSERT INTO mytable SET countries = '" . $countriesString . "'";

(Note: the string value is not escaped for example purpose, but it is not a good practice)

When you retrieve the value from the db, you retrive the string you put into, and obtain again an array with $array = explode(",", $retrievedValueFromDb), providing the same separator.

Update:

4) Retrieve values find_in_set() MySQL function

You can rely also on the find_in_set(needle, field) function, that returns an integer greater than zero if the string is present in a comma-separated value field.

So if you use approach 1) and have a string field like "USA,Germany", you can obtain the columns in which USA is present by using:

SELECT * FROM myTable WHERE find_in_set('USA', countries) <> 0

---

Approach 1) works if you have few fixed values, also very easy on queries. Downside is that you are limited to 64 values, that the values must be fixed, and the cannot contain a comma (,) as its used as a separator.

Approach 2) is the most standardized and correct, it's also normalized and query-efficient

Approach 3) is easy but requires extra processing and is not so easy for SQL

As stated elsewhere, there is no "array" datatype in the dbms, you have to use some workaround.