0

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?

Daniil
  • 87
  • 1
  • 12
  • 1
    What do you mean array? json array? – D-Shih Apr 18 '22 at 09:54
  • MySQL doesn't have arrays. Why do you wan them? As for `@variable`, are you sure that's array syntax or even MySQL syntax? `@variable` are SQL Server script variables and parameter names – Panagiotis Kanavos Apr 18 '22 at 10:02
  • @D-Shih I mean any group of items accessible by an index (not necessarily numeric, actually). Thank you for your fast reply. – Daniil Apr 18 '22 at 10:04
  • 1
    @PanagiotisKanavos session variables are defined as `@variable_name` in mysql – Shadow Apr 18 '22 at 10:04
  • @PanagiotisKanavos as Shadow has mentioned, I was indeed talking about server script `@variables` a.k.a. session `@variables`. There is a quite new JSON type for table columns but it seems to be inapplicable to `@variables` we are talking about. – Daniil Apr 18 '22 at 11:01
  • @Daniil why do you need this in the first place? Relational databases work with tables. The query engine can use indexes and statistics to execute queries far faster than any hand-rolled loop or brute-force search in memory. After all, the database server *already* caches data and indexes. Trying to second-guess the database will typically result in far slower code (orders of magnitude) and concurrency blocks. All those session variables you mention steal memory away from data caches and indexes and hint at attempts to pass data between functions or stored procedures from the back door – Panagiotis Kanavos Apr 18 '22 at 11:19
  • @PanagiotisKanavos it's true but still it seems like when directly accessed, a session array in RAM will work faster than a table (which, I assume, can be stored either in RAM or in HDD, subjects to situation). Is it not correct? Or can we be sure that when we call `JSON_VALUE(@object_json_string, @property)` the server will keep the parsed `object` in the RAM? – Daniil Apr 18 '22 at 11:28
  • 1
    Only if you ignore the time taken to load the data into the array, and the array is so small it doesn't matter anyway. Is an array faster than a dictionary? Yes, if it's only a few items. Using an array means you can't JOIN data, only iterate over them, which is the slowest way possible. What is faster, set operations between two HashSet objects, or set operations between two arrays that require nested loops? – Panagiotis Kanavos Apr 18 '22 at 11:34
  • @PanagiotisKanavos yes, data load will take time. But what if we need to create an array manually specially for this server script and are going to use the array in pure C-like style? Please, see the *UPDATE*! – Daniil Apr 18 '22 at 13:05
  • 1
    A `TEMPORARY TABLE` goes away when the connection is closed (or otherwise dies). A regular `TABLE` is permanent. `ENGINE=MEMORY` goes away when the machine is shutdown. – Rick James Apr 19 '22 at 14:14
  • 1
    Iterating over an array is likely to be very slow. Using the power of SQL to perform an operation to every row in a table 'simultaneously' is how you should design things. – Rick James Apr 19 '22 at 14:15
  • @RickJames Do you mean that `TEMPORARY TABLE` is stored in RAM? – Daniil Apr 20 '22 at 03:32
  • @RickJames According to all the comments, it seems like we can rely on MySQL optimization entirely. Even in case of array-like `TABLE`s and probably even in case of multiple parsing of JSON strings by `JSON_VALUE(...)`. Maybe, we should think like: 1) If arrays would be more efficient then they would be provided. 2) If parsed JSON arrays wouldn't be stored in RAM when necessary then there ould be provided an opportunity to manually handle their RAM storage. – Daniil Apr 20 '22 at 03:41
  • @RickJames could you, please, share how you would solve the following task: creation several identical tables with different names and fill them with the same content? E.g., each table can be an initial statistics of a fresh app user (and now you have multiple users to deal with). Of course, you can create a single table and put all users' statistics into the one but the thing is that you need to track it hour by hour, so such a single table would become tremendously large. – Daniil Apr 20 '22 at 14:02
  • 1
    @Daniil - `CREATE TABLE T_123 LIKE T_template; INSERT INTO T_123 SELECT * FROM T_template;` – Rick James Apr 20 '22 at 19:30
  • @RickJames Bright and clear! Thank you! – Daniil Apr 21 '22 at 11:52

1 Answers1

-2

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

Ahmad Bin Younas
  • 9
  • 2
    Copying the contents of a MySQL issue is *not* a good answer to anything. `No there isn't, but there's an issue for this, here's the link` would be a valid answer. – Panagiotis Kanavos Apr 18 '22 at 10:00
  • 3
    You may want to add the information to your answer, that this feature request is about 15 years old and hasn't been implemented yet. – Solarflare Apr 18 '22 at 10:05
  • 1
    @Solarflare and is still unassigned which means it's not going to be implemented any time soon – Panagiotis Kanavos Apr 18 '22 at 10:10
  • As far as I know, up until now, MySQL has yet to implement a real `array` data type. However, being an RDBMS, the utilization of array can be sated with table joins. Supposing we have a table named `users` which has `user_id int primary key, user_name varchar(30), account_is_active set('yes','no')`. We really want it to have a wishlist column which requires the use of an array. Then we can create a table named `wishlists` which has `user_id , item_on_wishlist varchar(30),foreign key (user_id) references users(user_id)`. By inner joining those two tables, we can simulate the use of an array. – blabla_bingo Apr 18 '22 at 10:29
  • @blabla_bingo During the research on array `@variables` I see mentions of `JOIN` here and there. But how can one implement idea of arrays using `JOIN`? I understand using a single table in order to achieve it but using `N` tables for this? Is it implied that `JOIN` can be used for creation of a 3D, 4D, ND arrays? Then it would be understandable. – Daniil Apr 18 '22 at 11:10
  • @blabla_bingo your example solution is beautiful. It still implements the idea of actually using a table as an array but you deftly attach the *keys*-table to the *values*-table. Of course, we could've combined the both columns in a single *array*-table but what if we want to use already existing *keys*? Then your solution is just what we need. – Daniil Apr 18 '22 at 11:15