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.

Mandatory Features Feature Firebird Point MySQL 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-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, MySQL can 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 1 Firebird doesn't have this.

Optional Features Feature Firebird Point MySQL Point Explanation F121-01 GET DIAGNOSTICS 1 Firebird doesn't have this. F200 TRUNCATE 1 Firebird doesn't have this. F201 CAST function 1 In Firebird I can cast to (among other data types) VARCHAR, SMALLINT, INT, BIGINT, NUMERIC, TIMESTAMP. In MySQL, I can't. 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. F312 Merge statement 1 MySQL doesn't have a MERGE statement, although it does have near equivalents. 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 allows CREATE GLOBAL TEMPORARY. MySQL has temporary tables in a non-standard way. F690 Collation support 1 See Note #2. T021 BINARY and VARBINARY data types 1 Firebird doesn't have this, although it does allow specifying a character set as OCTETS,which is comparable to MySQL's "binary". 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. T176 Sequence generator support 1 Firebird only supports a "CREATE SEQUENCE sequence-name" statement, but that's enough for a point. T211 Basic trigger capability See the table in an earlier post but MySQL has more standard CREATE TRIGGER syntax. Can't decide. 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 are beta.

Note #1: Re character sets: Ignoring duplicates, alias names, and slight variations: Firebird multi-byte character sets are gbk, sjis, utf-8, big5, gb2312, eucjp, ksc 5601, 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 multi-byte character set for which Firebird has many 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: 16. Total points for MySQL: 16.

Update: in an earlier edition of this post, I broke the tie by giving Firebird a delay-of-game penalty on the basis that the makers started talking about version 3.0 in 2011, and version 3.0 is still alpha, and I concluded "MySQL wins by a score of 16 to 16". That wasn't serious. The fair way to break the tie is to decide which features should have more weight.