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.
I am encouraging innovation and new things but some things should not be reinvented. The community should just choose 3-4 types of DBMS systems and grow them as we would be in a much more developed versions of those right now.
The community should just choose 3-4 types of DBMS systems…
3-4 types? Let’s see what we can come up with.
1. sqlite, yeah, that definitely has a place. I mean, small, simple, can be easily embedded. What’s not to like?
2. hsqldb, because we might need something sqlite-like for the java world.
3. MySql or Postgres, because we need a “normal” database as well. Something that isn’t just a file.
4. Cassandra. We’ll want something that is distributed. Not everyone needs ACID and cassandera obviously has its uses. And it is sort of no-sql.
5. Maybe we will want some sort of document database as well. Mongo? couchbase perhaps?
6. Some people will probably want something like a simple key-value store. Oh, and maybe distributed.
7. I might want ridiculous performance in a SQL database. There are a few in-memory databases that sacrifices safety for speed. Maybe those too have their place?
8. I don’t think we need to continue…
It’s not like one day everyone decided that we should all make different databases. They grew from different needs (and perhaps politics… or project management that won’t let you work on your great ideas). If there is one database that would objectively worse than any alternative, no one would use it.
We have this many databases because they serve a purpose. Limiting the number would probably mean that they got better for some, but not for all… Which would mean people would start writing new solutions to the problem that the limited number of database options causes.
I intend to delete posts which appear to be veeering off topic.
About E021-03 Character literals:
> select version(), x’41’;
+———————-+——-+
| version() | x’41’ |
+———————-+——-+
| 5.1.68-MariaDB-debug | A |
+———————-+——-+
Thank you. I erred. I have removed the mention of E021-03.
Re Note #1, multi-byte character sets: you missed BIG_5, EUCJ_0208, GB_2312, KSC_5601, UNICODE_FSS and CP943C, which are all present in Firebird 2.5.
Re Note #2, collations: Firebird 2.5 has multiple collations for no less than 27 character sets, including several multibyte charsets. For the widespread iso8859_1 set (Latin-1), 19 collations are available.
Thank you for the correction. I have updated the notes. I mentioned that I’m not counting aliases or slight variations of character sets, which is why unicode_fss and cp943c are not listed. As for the collations, I was still thinking of multi-byte character sets but failed to say so. And by the word “multiple” I meant “more than two”, so I’ve corrected by saying “many”. The count still favours MySQL, but the difference is far less than I originally indicated.
About “T021 BINARY and VARBINARY data types” and Firebird.
There are 2 ways that BINARY values/columns can be supported:
1- using BLOB fields which can store up to 2GB of raw/bytes
2- using OCTETS character set and existing CHAR() and VARCHAR() datatypes.
I now see that “T021” refers to something beyond supporting binary columns. Unfortunately, I don’t have a SQL Standard “decoder ring” so I am not sure what “T021” is supposed to refer to (even though I found 3 separate links to the spec).
Re T021 BINARY and VARBINARY: Firebird does have those, but by different names: [VAR]CHAR() CHARACTER SET OCTETS.
I’m also missing T176: SEQUENCE support. To my knowledge, MySQL still doesn’t have this. (Yes, I’m aware of AUTO_INCREMENT, but that’s another thing, and risky as well: it will re-use numbers if the most recent rows are deleted. Ouch!)
I didn’t count Firebird’s “CREATE SEQUENCE sequence_name” statement because it lacks some things that the standard asks for. I realize now, that was not proper according to the criteria that I described. I’ve added T176 Sequence generator support.
Firebird can grant rights not only to PUBLIC role, but to any stored object: procedure, trigger, view.
Firebird can have any number of triggers for the same action, MySQL 5.6 is limited to one.
Firebird doesn’t have separated BINARY type, it just allow to keep any binary data in CHAR/VARCHAR field with charset OCTETS.
I hope that what I said about granting is clear enough, certainly I had no intent to suggest that Firebird can’t grant to anything except PUBLIC. It was a good idea to at least mention triggers, so I added a row for T211. I see that three different commenters have mentioned that Firebird supports an OCTETS character set, and will update the “Explanation” column for T021.
Good point. I changed the order of the columns.
About F221:
http://www.firebirdsql.org/refdocs/langrefupd15-create-table.html#langrefupd15-ct-contextvars
“Any context variable that is assignment-compatible to the column datatype can be used as a default”
Context variables are listed here:
http://www.firebirdsql.org/refdocs/langrefupd25-variables.html
MySQL only permits CURRENT_TIMESTAMP. Not sure this should change the results, you will decide…
Thank you for this comparison.
Maybe the embedded feature of firebird is another advantage against mysql. It is available for free from firebird web site and you can use the same db file and functionality with the client/server version. From the developer perspective you only have to make a simple change in connectionstring.
I have never tested the embedded functionality of mysql because it is not available on their web site and I think that they require a paid license.
You might want to glance at Mr Anders Karlsson’s blog post where he tries to disambiguate the word “embedded”. That’s a really old post, but as far as I can see the main point is still true — libmysqld is still the essential library, and is still supplied, with the usual choice of GPL license or commercial.