MySQL will implement a data type, ARRAY, to store variable-sized arrays, in compliance with Standard SQL (SQL:2003) array functionality. Rationale --------- -- Needed functionality: ARRAY functionality is required by standard SQL. MySQL 4.1 currently has no ability to support arrays. -- Compatibility: Other DBMSs (e.g. Oracle10g) do provide array support.
Syntax ------ Add a new column data type: <data type> ARRAY [[<maximum_elements]] -- The ARRAY data type specification may be used anywhere in an SQL statement where a data type specification is legal, e.g. to define a column in CREATE TABLE, to define a variable, parameter, or function return in a stored procedure, and so on. -- The <data type> may be any data type supported (except for ARRAY itself, and REF, which MySQL does not support). It defines the type of data which the array will contain. -- The [<maximum_elements>] 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 are 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 is correct syntax for defining an array that will contain 5 elements. -- As shown in the syntax diagram, [<maximum_elements>] 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 [<maximum_elements>] is omitted, the maximum cardinality of the array defaults to 1000, which should also be the absolute maximum cardinality. Thus: -- [<maximum_elements>] defaults to 1000. -- [<maximum_elements>] 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 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 any 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 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 ability to compare ARRAY data. -- Assignment: -- See WL#2082 Add ARRAY element reference function and WL#2083 Add ARRAY value constructor function. Other statements ---------------- -- Two other syntax elements must be implemented in order 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(<array_expression>). See WL#2085. -- Array concatenation. See WL#. An example ---------- Create a table with the new data type: CREATE TABLE ArrayTable (array_column INT ARRAY); 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