MySQL Syntax 100% transferrable to MemSQL?
Or 90%? 70%?
Just trying to gauge if this is another separate SQL language to learn or are most things trasferrable.
CodePudding user response：
First thing: MemSQL no longer exists by that name. Their makers rebranded it as SingleStoreDB in 2020.
Users use MySQL client software to connect to the SingleStoreDB Cloud server. SingleStoreDB Cloud uses the same wire protocol as MySQL and supports similar SQL syntax as MySQL for compatibility with MySQL client software.
What they mean by "wire protocol" is that you can use the same client to connect to SingleStoreDB. You can connect and authenticate, send queries, and fetch results using the same client, but the SQL queries (which are just strings when you send them) are not necessarily compatible.
There are other database products that use the MySQL wire protocol: MariaDB, TiDB, ClickHouse, PlanetScale, and Dolt. They all have slightly different SQL language support.
How does one say what "percent" compatible they are?
Glancing through the documentation for MemSQL, there are a lot of MySQL-isms that look familiar, like auto-increment and many built-in functions and stored procedures and data types an INSERT...ON DUPLICATE KEY UPDATE and so on. I would guess that SingleStoreDB was based on a fork of MySQL some years ago.
SingleStoreDB has some proprietary features that aren't implemented in MySQL. Likewise, MySQL has features that aren't in SingleStoreDB (I just glanced at a section at random: JSON built-in functions, because it's a relatively recent addition to MySQL. I found SingleStoreDB is totally different from MySQL in this area).
But there are many features that seem to be compatible. Or are they? Both databases support common table expressions using the WITH syntax, but MySQL supports WITH RECURSIVE while SingleStoreDB does not.
But SingleStoreDB support its own extensions that MySQL has no equivalent for, like ARRAY and RECORD types.
If you could measure the SQL grammar of each database and count the terminals and nonterminals in each parse tree, and use that to measure a precise percentage of compatibility, what would you do with that information?
Suppose the answer is 80% of the grammar is common to these two databases. Does this mean the queries in your project will not need to be reviewed? No -- all your queries still need to be reviewed. Which means you need to test carefully and study the documentation of both products to understand the differences.
Sorry, there's no short-cut when you're porting code from one platform to the other. You just have to concentrate, study, and test, and this takes time.
By the way, you should also read this blog before choosing to support MemSQL: https://dom.as/2012/06/26/memsql-rage/ It's written by an senior MySQL developer, so it could be considered biased, but he's also a respected database performance expert and he supports his arguments well.
CodePudding user response：
MemSQL is becoming SingleStore now because of the nature of "universal storage" supporting OLAP and OLTP workload.
SingleStore supports all MySQL datatype and 95% of MySQL functions. On top of that SingleStore has other new functions and indexes / key.
The only thing that SingleStore doesn't support yet is foreign key, other than that you can just keep your MySQL schema and easily migrate your data into SingleStore.