SingleStore Managed Service

Other Types

Data Type

Size

Size (Not Null)

Max cardinality

Max values per row

ENUM

4 bytes

2 bytes

65,535 unique values

1

SET

12 bytes

8 bytes

64 unique values

64

BIT

9 bytes

8 bytes

Using the ENUM Data Type

An ENUM is a string value chosen from a user-defined list of values that are specified when creating a table. The values in an ENUM list must be quoted strings, which are distinct from others in the list. A column can be defined as ENUM using the following syntax:

CREATE TABLE table_name (  
    ---  
    col_name ENUM('val1','val2',...,'valN')
)

The values in an ENUM list are indexed, starting from 1. Consider a column defined as mode ENUM('Primary','Secondary','Tertiary'). The following table displays the index for each possible value that the column can have.

Value

Index

NULL

NULL

''

0

'Primary'

1

'Secondary'

2

'Tertiary'

3

The index of a NULL value is NULL. An empty string denotes an invalid value, and it has an index of 0. You can use the following query to identify the rows with invalid ENUM value assignments:

SELECT ...
WHERE enum_column = 0;

Important

The index of any ENUM value denotes its position in the list of values. It has no relation with table indexes.

ENUM values cannot be expressions or user-defined variables. In non-strict mode, an invalid value is inserted as an empty string. In strict mode, trying to insert an invalid values returns an error. ENUM values are sorted based on their index values. The NULL values are sorted first, then the empty strings (with index 0), followed by the values in the ENUM list.

The following example shows how ENUM values are defined and used.

CREATE TABLE delStat  
( ID VARCHAR(3),    
  Status ENUM('NOT_DISPATCHED','IN_TRANSIT','DELIVERED'));

INSERT INTO delStat VALUES('xdy',1);
INSERT INTO delStat VALUES('tsg','IN_TRANSIT');
INSERT INTO delStat(ID) VALUES('qfc');

SELECT * FROM delStat ORDER BY Status ASC;
****
+------+----------------+
| ID   | Status         |
+------+----------------+
| qfc  | NULL           |
| xdy  | NOT_DISPATCHED |
| tsg  | IN_TRANSIT     |
+------+----------------+

This example shows that if an integer values is specified, the ENUM value at that index is inserted. Because the NOT NULL constraint was not applied on the column, the default value inserted is NULL.

Using the SET Data Type

The SET data type restricts the values that can be inserted for a table column, and can have zero or more string values. However, the string value must exist in the list of values specified for the column at the time of table creation.

A SET column value can contain multiple set members, separated by commas. In such a case,

  • the SET member values should not individually contain commas.

  • the SET member values can be inserted in any order. The results are retrieved in the same order as listed at the time of table creation.

  • even if a SET member value is repeated, the results display the value only once.

The following example displays the usage of the SET data type:

Create a table column with SET data type.

CREATE TABLE setexample (col SET('v1', 'v2', 'v3', 'v4'));

Insert column values as v1,v2, v2,v1, v1,v4,v4, and v3,v4,v3.

INSERT INTO setexample (col) VALUES ('v1,v2'), ('v2,v1'), ('v1,v4,v4'), ('v3,v4,v3');

The result is retrieved in the same order in which the SET member values were listed during table creation and duplicate values are ignored:

SELECT col FROM setexample;
****
+------------------------------+
| col                          |
+------------------------------+
| v1,v2                        |
| v1,v2                        |
| v1,v4                        |
| v3,v4                        |
+------------------------------+
Using the BIT Data Type

The BIT data type is used to represent bit vectors. It stores bit values in binary notation, i.e., a binary value represented using 0s and 1s. A value of type BIT(n) can store n-bit values, where n can range from 1 to 64. However, SingleStore gives a warning if the size of BIT type column is not 64.

Bit-Value Literals

The BIT data type can also be used to store and represent bit-value literals. By default, a bit-value literal is stored as a binary string. In numeric expressions, a bit-value literal is treated as an integer equivalent of the BIT value. A bit-value literal can be specified using any of the following notations:

  • b'101' or B'101' - the leading b is case-insensitive

  • 0b101 - the leading 0b is case-sensitive

If you assign a value that is less than n bits long, the value is left-padded with 0s. For example, if you assign the value b'1100' to a BIT(7) column type, the assigned value is b'0001100'.

Examples

The following example shows that bit-value literals are stored as binary strings.

SELECT b'1100101' AS 'String Value', CHARSET(b'1100101') AS 'Charset';
****
+--------------+---------+
| String Value | Charset |
+--------------+---------+
| e            | binary  |
+--------------+---------+

The following example shows how bit-value literals behave as integers in a numerical expression.

SELECT b'1100101' + 9;
****
+----------------+
| b'1100101' + 9 |
+----------------+
|            110 |
+----------------+