MySQL to SingleStore Migration

SQLines tools can help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries and SQL scripts from MySQL to SingleStore.

Databases:

MySQL 8.x and 5.x
SingleStore 8.x and 7.x

Migration Reference

SQL Language Elements

Converting literals:

	MySQL			SingleStore
1	-- comment	# comment	Single-line comment	-- comment	# comment
2	_utf8'string'	_utf8mb4'string'	UTF-8 string literal	_utf8'string'

Data Types

Converting character data types:

	MySQL		Singlestore
1	VARCHAR(n)	Variable-length string, 0 <= n <= 65535	VARCHAR(n)	0 <= n <= 21845

Converting numeric data types:

	MySQL		Singlestore
1	MEDIUMINT [UNSIGNED]	24-bit integer	MEDIUMINT [UNSIGNED]
2	SMALLINT [UNSIGNED]	16-bit integer	SMALLINT [UNSIGNED]
3	TINYINT [UNSIGNED]	8-bit integer	TINYINT [UNSIGNED]

Converting datetime data types:

	MySQL		Singlestore
1	DATETIME(p)	Date and time data with fraction, 0 <= p <= 6, default is 0	DATETIME	No fraction
			DATETIME(6)	Only precision of 6 is accepted
2	TIMESTAMP(p)	Date and time data with fraction, 0 <= p <= 6, default is 0	TIMESTAMP	No fraction
			TIMESTAMP(6)	Only precision of 6 is accepted

Other data types:

	MySQL		Singlestore
1	ENUM(value, value2, …)	Enumeration (one of values from the list)	ENUM(value, value2, …)
2	SET(value, value2, …)	Set (one or more values from the list)	SET(value, value2, …)

CREATE TABLE Statement

Converting table definitions:

	MySQL		SingleStore
1	AUTO_INCREMENT	Auto-increment column property	AUTO_INCREMENT
2	ON UPDATE CURRENT_TIMESTAMP	On update rule	ON UPDATE CURRENT_TIMESTAMP
3	FOREIGN KEY (col) REFERENCES tab(col)	Foreign key constraint	Commented, not supported by SingleStore

CREATE PROCEDURE Statement

Converting stored procedures:

	MySQL		SingleStore
1	CREATE PROCEDURE [IF NOT EXISTS] name		CREATE OR REPLACE PROCEDURE name
2	IN \| OUT \| INOUT param datatype(len)	Parameter definition	param datatype(len)
3	LANGUAGE SQL		Removed
4	[NOT] DETERMINISTIC		Removed
5	READS SQL DATA		Removed
6	SQL SECURITY DEFINER		Removed
7	COMMENT 'text'		Commented

For more information, see Converting Procedural (SPL) Statements.

CREATE FUNCTION Statement

Converting user-defined functions (UDF):

	MySQL		SingleStore
1	CREATE FUNCTION [IF NOT EXISTS] name		CREATE OR REPLACE FUNCTION name
2	param datatype(len)	Parameter definition	param datatype(len)
3	LANGUAGE SQL		Removed
4	[NOT] DETERMINISTIC		Removed
5	READS SQL DATA		Removed
6	SQL SECURITY DEFINER		Removed
7	COMMENT 'text'		Commented

For more information, see Converting Procedural (SPL) Statements.

MySQL SPL Language

Converting MySQL procedural language (SPL).

Variable declarations:

	MySQL		SingleStore
1	DECLARE var type	Standalone DECLARE statements	DECLARE section before BEGIN … END block

SQL Statements

Converting SQL statements:

	MySQL		SingleStore
1	CREATE SCHEMA name …	Create schema	CREATE SCHEMA name …
2	DROP SCHEMA IF EXISTS name …	Drop schema	DROP SCHEMA IF EXISTS name …
3	USE name …	Set the default schema	USE name …

SQLines Data

SQLines SQL Converter

Migration to MariaDB

Migration to MySQL

Migration to PostgreSQL

Migration to SQL Server

Migration to Oracle

SQL in Applications