The Drizzle database, while similar to MySQL includes a number of significant differences. In this post we will look at data types and table syntax that is valid in Drizzle. For more background information you can also review A beginners look at Drizzle – Getting around with SHOW .
Data Types
This comparison is with Drizzle 2009.03.970 and MySQL 5.1.32 GA. More information at MySQL 5.1 Data Types .
The following data types are not valid in Drizzle.
- TINYINT
- SMALLINT
- MEDIUMINT
- BIT
- TIME
- YEAR
- BINARY
- SET
Tests used the following data-types in comparison with MySQL 5.1.
TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT, FLOAT,DOUBLE,DECIMAL,BIT, ENUM, SET, DATE,TIME,DATETIME,TIMESTAMP,YEAR, CHAR,VARCHAR,BINARY,VARBINARY, TEXT,TINYTEXT,MEDIUMTEXT,LONGTEXT, BLOB,TINYBLOB,MEDIUMBLOB,LONGBLOB
I’m surprised that a number of data types are still valid in Drizzle however the product is still under heavy development. My kill-list for further reducing the syntax scope would include TINYTEXT,MEDIUMTEXT,LONGTEXT, TINYBLOB,MEDIUMBLOB,LONGBLOB
Numeric sizing for INT,BIGINT,FLOAT datatypes is not supported. i.e. INT(1). Very glad about that.
The UNSIGNED and ZEROFILL syntax for Numeric datatypes are not supported.
Character Sets
Another difference is the lack of the CHARACTER SET syntax for character fields. Drizzle only supports the UTF8 character set for all text fields. This also differs from MySQL, as it is a 4 byte field, not 3 byte in MySQL.
drizzle> select * from information_schema.character_sets;
+--------------------+----------------------+---------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+---------------+--------+
| utf8 | utf8_general_ci | UTF-8 Unicode | 4 |
| binary | binary | | 1 |
+--------------------+----------------------+---------------+--------+
2 rows in set (0.09 sec)
mysql> select * from information_schema.character_sets where character_set_name in ('utf8','binary');
+--------------------+----------------------+-----------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+-----------------------+--------+
| utf8 | utf8_general_ci | UTF-8 Unicode | 3 |
| binary | binary | Binary pseudo charset | 1 |
+--------------------+----------------------+-----------------------+--------+
2 rows in set (0.00 sec)
Collations (the COLLATE syntax) is still supported.
drizzle> select * from information_schema.collations order by 1;
+--------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+--------------------+--------------------+-----+------------+-------------+---------+
| binary | binary | 63 | Yes | Yes | 1 |
| utf8_bin | utf8 | 46 | | Yes | 1 |
| utf8_czech_ci | utf8 | 234 | | Yes | 8 |
| utf8_danish_ci | utf8 | 235 | | Yes | 8 |
| utf8_esperanto_ci | utf8 | 241 | | Yes | 8 |
| utf8_estonian_ci | utf8 | 230 | | Yes | 8 |
| utf8_general_ci | utf8 | 45 | Yes | Yes | 1 |
| utf8_hungarian_ci | utf8 | 242 | | Yes | 8 |
| utf8_icelandic_ci | utf8 | 225 | | Yes | 8 |
| utf8_latvian_ci | utf8 | 226 | | Yes | 8 |
| utf8_lithuanian_ci | utf8 | 236 | | Yes | 8 |
| utf8_persian_ci | utf8 | 240 | | Yes | 8 |
| utf8_polish_ci | utf8 | 229 | | Yes | 8 |
| utf8_romanian_ci | utf8 | 227 | | Yes | 8 |
| utf8_roman_ci | utf8 | 239 | | Yes | 8 |
| utf8_sinhala_ci | utf8 | 243 | | Yes | 8 |
| utf8_slovak_ci | utf8 | 237 | | Yes | 8 |
| utf8_slovenian_ci | utf8 | 228 | | Yes | 8 |
| utf8_spanish2_ci | utf8 | 238 | | Yes | 8 |
| utf8_spanish_ci | utf8 | 231 | | Yes | 8 |
| utf8_swedish_ci | utf8 | 232 | | Yes | 8 |
| utf8_turkish_ci | utf8 | 233 | | Yes | 8 |
| utf8_unicode_ci | utf8 | 224 | | Yes | 8 |
+--------------------+--------------------+-----+------------+-------------+---------+
23 rows in set (0.09 sec)
mysql> select * from information_schema.collations where character_set_name in ('utf8','binary') order by 1;
+--------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+--------------------+--------------------+-----+------------+-------------+---------+
| binary | binary | 63 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
| utf8_czech_ci | utf8 | 202 | | Yes | 8 |
| utf8_danish_ci | utf8 | 203 | | Yes | 8 |
| utf8_esperanto_ci | utf8 | 209 | | Yes | 8 |
| utf8_estonian_ci | utf8 | 198 | | Yes | 8 |
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_hungarian_ci | utf8 | 210 | | Yes | 8 |
| utf8_icelandic_ci | utf8 | 193 | | Yes | 8 |
| utf8_latvian_ci | utf8 | 194 | | Yes | 8 |
| utf8_lithuanian_ci | utf8 | 204 | | Yes | 8 |
| utf8_persian_ci | utf8 | 208 | | Yes | 8 |
| utf8_polish_ci | utf8 | 197 | | Yes | 8 |
| utf8_romanian_ci | utf8 | 195 | | Yes | 8 |
| utf8_roman_ci | utf8 | 207 | | Yes | 8 |
| utf8_slovak_ci | utf8 | 205 | | Yes | 8 |
| utf8_slovenian_ci | utf8 | 196 | | Yes | 8 |
| utf8_spanish2_ci | utf8 | 206 | | Yes | 8 |
| utf8_spanish_ci | utf8 | 199 | | Yes | 8 |
| utf8_swedish_ci | utf8 | 200 | | Yes | 8 |
| utf8_turkish_ci | utf8 | 201 | | Yes | 8 |
| utf8_unicode_ci | utf8 | 192 | | Yes | 8 |
+--------------------+--------------------+-----+------------+-------------+---------+
22 rows in set (0.00 sec)
Perhaps a new MySQL 6.0 collation that is in Drizzle is ‘utf8_sinhala_ci’.
Storage Engines
A key difference in Drizzle is the default storage engine. This defaults to InnoDB, rather then MyISAM. MyISAM is still currently packaged with Drizzle, however I hope that Maria becomes it’s replacement.
The MERGE and FEDERATED storage engines are not supported.
BLACKHOLE by default is not supported, however documentation indicates this can be compiled.
Table Syntax
FULLTEXT indexes which is valid for MyISAM only in MySQL is not supported.
PARTITIONS is not supported.
The following table options are still valid in Drizzle. These were only tested as valid syntax, not any usage of this functionality.
ENGINE, AUTO_INCREMENT, AVG_ROW_LENGTH, CHECKSUM, COLLATE, COMMENT, CONNECTION, DELAY_KEY_WRITE, KEY_BLOCK_SIZE, PACK_KEYS
INSERT_METHOD, CHARACTER SET is not support.
I suspect a number of these could be eliminated, definitely CONNECTION as this is related to the FEDERATED storage engine.
VIEWS are not supported in Drizzle.
I will need to invest more time to confirm INDEX and CONSTRAINT syntax.
