MySQL versus Firebird

Firebird is an open-source DBMS with a long history and good SQL support. Although I measured Firebird's importance as smaller than MySQL's or MariaDB's , it exists, and might grow a bit when Firebird becomes the default LibreOffice DBMS .

I decided to compare the current versions of MySQL 5.6 and Firebird SQL 2.5. I only looked at features that end users can see, without benchmarking. Preparing for the comparison was easy. I looked at the instructions for downloading Firebird with Ubuntu and within 15 minutes I was entering SQL statements. The documentation is disorganized but when in doubt I could guess what to do, since the Firebird folks care about standards.

I'll organize the discussion according to the ANSI/ISO feature list. I'll skip the features which both products support equally, which means I'll skip the great majority, because both products are very similar. Then: For every feature where MySQL is better, MySQL gets a point. For every feature where Firebird is better, Firebird gets a point. The only way to be objective is to be arbitrary.

firebird dolphin

Mandatory Features

Feature MySQL Point Firebird Point Explanation
E011-03 DECIMAL and NUMERIC types 1 Firebird maximum number of digits = 18. MySQL maximum = 65.
E021-02 CHARACTER VARYING data type 1 Firebird maximum VARCHAR length = 32767. MySQL maximum = 65535.
E021-03 Character literals 1 Hex representation in Firebird is X'41'. In MySQL it's 0x41.
E021-07 Character concatenation 1 Firebird concatenation operator is ||. MySQL has CONCAT() by default.
E031-01 Delimited identifiers 1 Firebird delimiter "" means case-sensitive. MySQL has no viable equivalent .
E041-04 Basic foreign key 1 In Firebird one can use syntax variants that MySQL/InnoDB ignores.
E101-03 Searched UPDATE statement 1 Firebird has fixed a "small flaw . MySQL still has the flaw.
E141-08 CHECK clause 1 MySQL doesn't have this.
F031-03 GRANT statement 1 1 Only Firebird can grant to PUBLIC . But only MySQL can grant TRIGGER . One point each.
F051-03 TIMESTAMP data type 1 Firebird can have 4 digits for seconds precision, MySQLcan have 6.
T321-01 User-defined SQL functions 1 Firebird only has procedures, and they're PLSQL syntax . MySQL has standard syntax.
9075-11 information_schema Firebird doesn't have this.

firebird dolphin

Optional Features

Feature MySQL Point Firebird Point Explanation
F121-01 GET DIAGNOSTICS 1 Firebird doesn't have this.
F200 TRUNCATE 1 Firebird doesn't have this.
F221 Explicit defaults 1 Both products allow DEFAULT in CREATE statements, but only MySQL allows DEFAULT in UPDATE or INSERT statements.
F251 Domain support 1 MySQL doesn't have this.
F391 Long identifiers 1 Firebird maximum length = 31. MySQL maximum length = 64. Both are sub-standard.
F401-02 Full outer join 1 MySQL doesn't have this.
F461 Named character sets 1 See Note #1.
F531 Temporary tables 1 Firebird doesn't have this. MySQL has it in such a non-standard way that it deserves less than nothing.
F690 Collation support 1 See Note #2.
T021 BINARY and VARBINARY data types 1 Firebird doesn't have this.
T301 Boolean data type 1 Firebird doesn't have this. MySQL has it in such a non-standard way that it deserves less than nothing.
T121 WITH (excluding RECURSIVE) in query 1 Firebird recently added WITH support . MySQL doesn't have it. Incidental note: misinformation has been spread about MySQL's history for this feature, for example the first comment on this blog post . The fact is that in 2004 MySQL decided to support CONNECT BY, not WITH. Then it didn't implement it.
T131 Recursive query 1 MySQL doesn't have this.
T141 SIMILAR predicate 1 MySQL doesn't have this.
T171 LIKE clause in table definition 1 Firebird doesn't have this.
T172 AS clause in table definition 1 Firebird doesn't have this.
T281 SELECT privilege with column granularity 1 Firebird doesn't have this.
T331 Basic roles 1 Firebird has 50% support. MySQL has nothing. MariaDB roles arebeta.

Note #1: Re character sets: Ignoring duplicates, alias names, and slight variations: Firebird multi-byte character sets are gbk, sjis , utf-8, gb18030 . MySQL multi-byte character sets are gbk, sjis, utf-8, big5, gb2312, eucjp, euckr, utf-16, utf-32. As I put it in an earlier blog post, "The UTF-8 world is not enough" . MySQL has more, so it gets the point.

Note #2: Re collations: Although Firebird deserves special mention for supporting a slightly later version of the Unicode Collation Algorithm , the only character set for which Firebird has multiple collations is UTF-8. Firebird has only four collations for UTF-8: ucs_basic (code point order), and unicode / unicode_ci / unicode_ci_ai (variations of UCA order with and without case sensitivity or accent sensitivity). MySQL has additionally: Croatian, Czech, Danish, Estonian, German, Hungarian, Iceland, Latvian, Lithuanian, Persian, Polish, Romanian, Sinhala, Slovak, Slovenian, Spanish, Swedish, Turkish, Vietnamese. Incidental side note: guess whom the Firebird manual points to for extra documentation about its character sets? None other than the guy who coded MySQL's collations, a certain A. Barkov, formerly of MySQL, now of MariaDB.

Total points for Firebird: 15. Total points for MySQL: 15.

So, as a tiebreaker, I will award a delay of game penalty to Firebird, seeing that the makers started talking about version 3.0 in 2011 , and version 3.0 is still alpha .

MySQL wins by a score of 15 to 15.