← Back to Mist Documentation

MySQL Compatibility Reference

Comprehensive overview of MySQL syntax support in the Mist database engine. Features are categorized by implementation status with detailed notes on limitations and usage.

✅ Fully Supported - Complete implementation with all major features
⚠️ Partially Supported - Basic implementation with some limitations
❌ Not Supported - Not implemented
📝 Parsed Only - Syntax accepted but not enforced/executed

🆕 Recent Enhancements

The following features have been recently implemented, significantly expanding MySQL compatibility:

🔍 Pattern Matching

  • LIKE/NOT LIKE with % and _ wildcards
  • Case-sensitive pattern matching
  • Escaped literal support (\%, \_)
  • Full WHERE and JOIN integration
WHERE name LIKE 'John%'
WHERE email NOT LIKE '%@temp.com'

🚫 Logical NOT Operator

  • NOT operator for all conditions
  • Complex expression negation
  • Double negation support
  • Integration with all existing operators
WHERE NOT active
WHERE NOT (age < 18 OR status = 'temp')

🔍 EXISTS Subqueries

  • EXISTS/NOT EXISTS subqueries
  • Non-correlated subquery support
  • Complex condition integration
  • Proper NULL handling
WHERE EXISTS (SELECT 1 FROM...)
WHERE NOT EXISTS (SELECT * FROM...)

📊 Scalar Subqueries

  • Single-value subqueries in SELECT and WHERE
  • Aggregate function support
  • Proper error handling for multiple rows/columns
  • NULL handling for empty results
SELECT (SELECT MAX(price) FROM products)
WHERE dept_id = (SELECT id FROM departments WHERE...)

💡 Migration Impact

These enhancements significantly reduce the need for application-level workarounds when migrating from MySQL. Pattern matching, logical operations, and subqueries now work natively, improving both performance and code simplicity. Scalar subqueries enable complex data retrieval patterns common in MySQL applications.

Data Definition Language (DDL)

Table Operations

Statement Status Features Limitations
CREATE TABLE ✅ Full Constraints, foreign keys, auto-increment, ENUM, DATE types No temporary tables, partitioning
ALTER TABLE ADD COLUMN ✅ Full Add columns with constraints and defaults —
ALTER TABLE DROP COLUMN ✅ Full Remove columns, auto-drop related indexes —
ALTER TABLE MODIFY COLUMN ✅ Full Change column type with data conversion —
DROP TABLE ✅ Full Full support with foreign key constraint checking Supports IF EXISTS clause
TRUNCATE TABLE ✅ Full Reset table data and auto-increment counter Validates foreign key constraints

Index Operations

Statement Status Features Limitations
CREATE INDEX ✅ Enhanced Single-column hash indexes (functional), composite indexes (parsed), full-text indexes (parsed) Composite and full-text indexes parsed but not functional
DROP INDEX ✅ Full Remove indexes —
SHOW INDEX FROM table ✅ Full Display table indexes —

Data Manipulation Language (DML)

INSERT Operations

Feature Status Notes
INSERT INTO ... VALUES ✅ Full Single and multiple row inserts
INSERT INTO ... (columns) VALUES ✅ Full Column-specific inserts
Auto-increment handling ✅ Full Automatic ID generation
Default value processing ✅ Full Including CURRENT_TIMESTAMP
INSERT ... SELECT ✅ Full Complete support with JOIN, aggregates, and complex queries
INSERT ... ON DUPLICATE KEY UPDATE ✅ Full With VALUES() function and expression support

SELECT Operations

Feature Status Supported Syntax Limitations
Basic SELECT ✅ Full Column names, wildcards (*), aliases, functions, arithmetic expressions, CASE statements —
WHERE clause ✅ Enhanced =, !=, <, <=, >, >=, AND, OR, NOT, IS NULL, IS NOT NULL, BETWEEN, NOT BETWEEN, IN, NOT IN, LIKE, NOT LIKE, EXISTS, NOT EXISTS, functions, arithmetic expressions EXISTS limited to non-correlated subqueries
JOIN operations ✅ Full INNER, LEFT, RIGHT, CROSS joins with ON conditions including functions and expressions —
Subqueries in FROM ✅ Full Virtual table creation from SELECT statements —
Scalar subqueries ✅ Full Non-correlated subqueries in SELECT and WHERE clauses Correlated subqueries not yet supported
LIMIT clause ✅ Full LIMIT count and LIMIT offset, count —
GROUP BY ✅ Full Works with aggregate functions —
ORDER BY ⚠️ Basic Basic column ordering Limited implementation, may vary
HAVING ✅ Full Aggregate function conditions —
UNION ✅ Full UNION and UNION ALL with duplicate handling —
Window functions ❌ None — ROW_NUMBER(), RANK(), etc. not implemented

Data Types

Numeric Types

Type Status Mapping Notes
TINYINT, SMALLINT, INT, BIGINT ✅ Full INT All integer types map to INT
DECIMAL(p,s) ✅ Full DECIMAL With precision and scale
FLOAT, DOUBLE ✅ Full FLOAT DOUBLE maps to FLOAT
BIT ✅ Full BOOL Maps to boolean

String Types

Type Status Mapping Notes
CHAR(n), VARCHAR(n) ✅ Full VARCHAR With length validation
TEXT, LONGTEXT ✅ Full TEXT All text types map to TEXT
ENUM('val1','val2') ✅ Full ENUM With value validation
BINARY, VARBINARY ⚠️ Basic VARCHAR No binary handling
SET('val1','val2') ✅ Full SET Comma-separated values with validation

Date and Time Types

Type Status Mapping Notes
DATE ✅ Full DATE String-based storage
DATETIME, TIMESTAMP ✅ Full TIMESTAMP With CURRENT_TIMESTAMP support
TIME ✅ Full TIME HH:MM:SS format with microseconds support
YEAR ✅ Full YEAR 1901-2155 range, 2/4-digit format support

Functions and Operators

Aggregate Functions

Function Status Supported Syntax Limitations
COUNT(*), COUNT(column) ✅ Full Count all rows or non-null values Only in SELECT clause with GROUP BY
COUNT(DISTINCT column) ✅ Full Count unique values Only in SELECT clause with GROUP BY
SUM(column), AVG(column) ✅ Full Numeric columns only Only in SELECT clause with GROUP BY
MIN(column), MAX(column) ✅ Full Any comparable type Only in SELECT clause with GROUP BY

Built-in Functions

Category Functions Status Notes
String Functions CONCAT(), SUBSTRING(), LENGTH(), UPPER(), LOWER(), TRIM() ✅ Full Available in SELECT, WHERE, and JOIN clauses
Date/Time Functions NOW(), CURDATE(), YEAR(), MONTH(), DAY(), DATE_FORMAT() ✅ Full Available in SELECT, WHERE, and JOIN clauses
Math Functions ABS(), ROUND(), CEILING(), FLOOR(), MOD(), POWER() ✅ Full Available in SELECT, WHERE, and JOIN clauses
Conditional Functions IF(), CASE...WHEN, COALESCE(), IFNULL(), NULLIF() ✅ Full Available in SELECT, WHERE, and JOIN clauses
Type Conversion CAST(), CONVERT() ✅ Full Available in SELECT, WHERE, and JOIN clauses

Special Functions

Function Status Supported Context Notes
VALUES(column) ✅ Full ON DUPLICATE KEY UPDATE expressions —
CURRENT_TIMESTAMP ✅ Full DEFAULT values in CREATE/ALTER TABLE Not available in SELECT or WHERE

Operators and Expressions

Type Operators Status Supported Context Limitations
Arithmetic +, -, *, /, % ✅ Full SELECT, WHERE, and JOIN clauses —
Comparison =, !=, <, <=, >, >= ✅ Full WHERE clauses, JOIN ON conditions —
Logical AND, OR ✅ Full WHERE clauses —
Logical NOT ✅ Full WHERE clauses, JOIN ON conditions —
Pattern Matching LIKE, NOT LIKE ✅ Full WHERE clauses, JOIN ON conditions Supports % and _ wildcards
Set Operations IN, NOT IN ✅ Full WHERE clauses, JOIN ON conditions —
Range BETWEEN ... AND ..., NOT BETWEEN ✅ Full WHERE clauses, JOIN ON conditions —
NULL Testing IS NULL, IS NOT NULL ✅ Full WHERE clauses, JOIN ON conditions —
Subquery EXISTS, NOT EXISTS ⚠️ Partial Non-correlated subqueries Correlated subqueries not yet supported
Scalar Subqueries (SELECT column FROM table WHERE ...) ✅ Full SELECT and WHERE clauses, with aggregates Non-correlated only; proper NULL and error handling

Constraints and Keys

Constraint Status Implementation Notes
PRIMARY KEY ✅ Full Uniqueness enforced —
UNIQUE ✅ Full Duplicate prevention —
NOT NULL ✅ Full Null validation —
AUTO_INCREMENT ✅ Full Automatic value generation —
DEFAULT value ✅ Full Including functions —
FOREIGN KEY ✅ Full Full referential integrity CASCADE, SET NULL, SET DEFAULT, RESTRICT
CHECK 📝 Parsed Syntax accepted, not enforced Improves migration compatibility

Transaction Support

Feature Status Notes
START TRANSACTION, BEGIN ✅ Full Begin transaction
COMMIT, ROLLBACK ✅ Full Commit or rollback changes
Nested transactions ✅ Full Full support with proper nesting
SAVEPOINT name ✅ Full Create savepoints
ROLLBACK TO SAVEPOINT ✅ Full Partial rollback
RELEASE SAVEPOINT ✅ Full Remove savepoint
Isolation levels 📝 Parsed SET TRANSACTION ISOLATION LEVEL statements accepted, not enforced
LOCK TABLES, UNLOCK TABLES 📝 Parsed Statements accepted, locking not enforced

📝 Parse-Only Features

These features are parsed and acknowledged but not functionally implemented. This improves compatibility with MySQL scripts and migration tools.

Feature Syntax Support Migration Benefit Example
CHECK Constraints 📝 Full Parsing MySQL dump files import without modification CHECK (price > 0)
Transaction Isolation Levels 📝 Full Parsing Migration scripts work without SET statement removal SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Table Locking 📝 Full Parsing Backup/restore scripts execute successfully LOCK TABLES users WRITE
Session Variables 📝 Full Parsing Configuration statements don't cause errors SET sql_mode = 'STRICT_TRANS_TABLES'

🔄 Migration Workflow

These parse-only features enable seamless migration from MySQL by accepting common SQL constructs without requiring script modification. While the functionality isn't enforced, the syntax compatibility significantly reduces migration friction.

Performance and Optimization

Indexing

Feature Status Notes
Hash indexes ✅ Full Single-column, equality lookups
Index-optimized queries ✅ Full Automatic usage in WHERE clauses
Composite indexes 📝 Parsed Syntax recognized and stored but no query optimization
Full-text indexes 📝 Parsed Syntax recognized and stored but no text search functionality

Syntax Limitations and Considerations

Expression Limitations

Feature Status What Works What Doesn't Work
SELECT Expressions ✅ Full Column names, aliases, aggregates, functions, calculations, CASE expressions —
WHERE Expressions ✅ Enhanced Comparisons, AND/OR/NOT logic, IS NULL/NOT NULL, BETWEEN/NOT BETWEEN, IN/NOT IN, LIKE/NOT LIKE, EXISTS/NOT EXISTS (basic), functions, arithmetic expressions Correlated subqueries, REGEXP
JOIN Conditions ✅ Enhanced Comparisons, AND/OR/NOT logic, IS NULL/NOT NULL, BETWEEN/NOT BETWEEN, IN/NOT IN, LIKE/NOT LIKE, functions, arithmetic expressions EXISTS subqueries, REGEXP
ORDER BY ⚠️ Basic Column names Expressions, functions, multiple criteria

Enhanced MySQL Features Now Supported

✅ Functions in SELECT

  • SELECT UPPER(name) FROM users ✅
  • SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users ✅
  • SELECT IF(age >= 18, 'Adult', 'Minor') FROM users ✅
  • SELECT NOW(), CURDATE() ✅

✅ Calculated Columns

  • SELECT price * quantity AS total FROM orders ✅
  • SELECT age + 1 AS next_year FROM users ✅
  • SELECT salary / 12 AS monthly_salary FROM employees ✅

✅ Enhanced WHERE Conditions

  • WHERE age BETWEEN 18 AND 65 ✅
  • WHERE department IN ('Sales', 'Marketing') ✅
  • WHERE salary IS NOT NULL ✅
  • WHERE price NOT BETWEEN 10 AND 20 ✅
  • WHERE category NOT IN ('Legacy', 'Discontinued') ✅

✅ Newly Supported Pattern Matching

  • WHERE name LIKE 'John%' ✅
  • WHERE name NOT LIKE 'Admin%' ✅
  • WHERE name LIKE 'J_hn' ✅ (underscore wildcard)
  • WHERE NOT (price > 100 AND category = 'Premium') ✅

✅ Newly Supported Subqueries

  • WHERE EXISTS (SELECT 1 FROM categories WHERE id = 1) ✅
  • WHERE NOT EXISTS (SELECT 1 FROM archived_users WHERE name = 'test') ✅

❌ Still Not Supported WHERE Conditions

  • WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = users.id) ❌ (correlated subqueries)

Remaining Limitations

❌ Advanced Pattern Matching

  • WHERE email REGEXP '.*@company\.com' ❌
  • WHERE name RLIKE '^[A-Z].*' ❌

❌ Subqueries in SELECT

  • SELECT (SELECT MAX(salary) FROM employees) AS max_salary ❌
  • SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = users.id) FROM users ❌

General Database Limitations

Migration from MySQL

✅ Supported Migrations

  • Table structures with standard data types including TIME, YEAR, SET
  • CRUD operations with comprehensive WHERE conditions
  • Relationships with foreign keys and constraints
  • Complex reporting queries with aggregates, GROUP BY, and HAVING
  • Advanced JOINs with complex ON conditions
  • UNION operations for combining results
  • Function-based transformations and calculations
  • Conditional logic with IF and CASE expressions
  • String manipulation and date/time processing
  • Type conversions and casting

⚠️ Manual Adjustments Needed

  • Replace correlated subqueries with separate queries and application logic
  • Replace REGEXP/RLIKE patterns with LIKE or application filtering
  • Remove stored procedures, triggers, and views
  • Add explicit transactions where needed
← Back to Mist Documentation