MySQL: Can we create an array (associative or not) as a MySQL server script variable? [duplicate]

Question

I've seen a lot of SQL server script variables of this kind: @variable. But can we, in fact, store an array (associative or not) behind the @variable?

UPDATE

This question turns out to be a duplicate of this one, which suggests to consider possible using of:

• SET type and JSON type, which seem to be only column types but not @variable types.
• A TEMPORARY TABLE, which seem to be stored in HDD (right?).
• Functions working with JSON strings (e.g., JSON_VALUE and JSON_LENGTH), which are usable entirely within MySQL server script. Although, these functions do not help to derive an array and store it in a @variable and are merely JSON walkarounds. I would accept this variant but it seems like @json_string is parsed each time we call JSON_VALUE(@json_string).

So, till now it seems that there IS an opportunity to CREATE an array (associative or not!) but there IS NO an opportunity to surely KEEP the array for its further processing!

Regarding the question mentioned in the beginning of this one. Right now I've only reached 5th and 6th answers, which are related to JSON strings. They are interesting! Be sure to check them out if you're interested in the subject!

Thanks to everyone for your time!

UPDATE

As @Panagiotis Kanavos has mentioned, fetching data by value is slower in case of arrays.

But what if:

• We indeed want to simply iterate over M input arrays simultaneously and produce N output arrays? (Maybe, we are simply interested in collation of parameters along a timeline and keep the results.) Arrays are perfectly suitable for this task. But of course, in this case we can still use tables. The question is what will be faster? If our iterative process involves many requests to arrays' elements (can we rely on the server caching the M input arrays and that they'll always be at hand?) and creation of multiple result arrays (how long will it take in case of tables and how do we know that tables are created in RAM for fast access?)?

• We want to create an array manually along the course of a server script and are going to only use it in C-like style (aren't going to fetch its data by value) and after the script execution there'll be no need in the array? So, this will be a classic C-like script-only array. To me, in this case putting the array directly into the RAM is what we need and will be more effective than table creation (which'll probably go to HDD), won't it?

And so, the 2nd (and more general) question arises: How far can we rely on the server's optimizations?

I understand that a huge work's been put in optimization in many ways. But has somebody met a situation when a server didn't optimize in the best way? When a programmer had to explicitly rearrange the code in order to manually bring it to the optimal state?

Answer 1

MySQL will implement a data type, ARRAY, to store variable-sized arrays, in compliance with Standard SQL (SQL:2003) array functionality.

Syntax

Add a new column data type: ARRAY [[ may be any data type supported (except for ARRAY itself, and REF, which MySQL does not support). It defines the type of data that the array will contain.

-- The [] must be an unsigned integer greater than zero. It defines the maximum cardinality of the array, rather than its exact size. Note that the inner set of brackets is mandatory when defining an array with a specific size, e.g. INT ARRAY is correct for defining an array with the default size, INT ARRAY[5] is the correct syntax for defining an array that will contain 5 elements. -- As shown in the syntax diagram, [] is optional. If omitted, the maximum cardinality of the array defaults to an implementation-defined default value. Oracle's VARRAY size is limited to the maximum number of columns allowed in a table, so I suggest we make our default match that maximum. Thus, if [] is omitted, the maximum cardinality of the array defaults to 1000, which should also be the absolute maximum cardinality. Thus: -- [] defaults to 1000. -- [] may range from 1 to 1000.

Function

An array is an ordered collection of elements, possibly containing data values. The ARRAY data type will be used to store data arrays in database tables.

Rules

-- An array is an ordered set of elements.

-- The maximum number of elements in the array is known as the array's maximum cardinality. The maximum cardinality is defined at the time the array is defined, as is the element data type.

-- The actual number of elements that contain data values is known as the array's cardinality. The cardinality of an array may vary and is not defined at the time the array is defined. That is, an instance of an array may always contain fewer elements than the maximum cardinality allows.

-- Each element may contain a data value that corresponds to the array's defined data type. -- Each element has three states: blank (no value assigned to the element), NULL (NULL assigned to the element), and containing the valid value (data value assigned to the element).

-- Each element is associated with exactly one ordinal position in the array. The first array element is found at position 1 (one), the next at position 2 (two), and so on. Thus, assuming n is the cardinality of an array, the ordinal position of an array element is an integer in the range 1 (one) <= element <= n.

-- An array has a maximum cardinality and an actual cardinality. -- It is an error if one attempts to assign a value to an array an element whose position is greater than the maximum cardinality of the array. -- It is not an error if one attempts to assign values to only some of an array's elements.

-- Privileges: -- No special privileges are required to create a table with the ARRAY data type, or to utilize ARRAY data.

-- Comparison: -- See WL#2084 Add the ability to compare ARRAY data.

-- Assignment: -- See WL#2082 Add ARRAY element reference function and LW #2083 Add ARRAY value constructor function.

Other statements

-- Two other syntax elements must be implemented for the ARRAY data type to be useful. See WL#2082 for Array Element Reference syntax and WL#2083 for Array Constructor syntax. -- Also related: -- CARDINALITY(). See WL#2085. -- Array concatenation. See WL#.

An example

Create a table with the new data type: CREATE TABLE ArrayTable (array_column INT ARRAY[3]);

Insert data: INSERT INTO ArrayTable (array_column) VALUES (ARRAY[10,20,30]);

Retrieve data: SELECT array_column from ArrayTable WHERE array_column <> ARRAY[]; -- Returns all cases where array_column is not an empty array

Reserved words

ARRAY, eventually CARDINALITY