Migrating from MySQL? A Hands-On Guide to KingbaseES Compatibility
MySQL Compatibility Practices: From SQL Syntax Adaptation to Smooth Business System Migration
I. In database migration, the real trouble often isn't the data itself
In enterprise application development, MySQL is the first database many projects encounter. E-commerce platforms, order systems, operations backends, enterprise management systems, and even some data middle platforms all show its presence.
Over time, a database accumulates not just business data but a large amount of code bound to the database.
For example, MyBatis XML in Java projects, stored procedures, database triggers, scheduled tasks, and various report SQL. These things may not be noticeable normally, but when a project needs to change databases, you find there are many dependencies among them.
Some teams think database migration is simply exporting the original table structures, recreating them in the new database, and importing the data.
Only after starting implementation do they realize: being able to create table structures doesn't mean the original SQL will execute; being able to execute SQL doesn't mean the query results are completely consistent; having consistent query results doesn't mean the application runs properly.
For example, auto-increment fields, date functions, string comparison, pagination syntax, and duplicate key handling common in MySQL may have implementation differences across databases.
Therefore, MySQL compatibility is not just a syntax issue; it is a whole set of adaptation work involving database objects, SQL behavior, application interfaces, and runtime environment.
KingbaseES from CETC provides a MySQL compatibility mode covering data types, built-in functions, SQL syntax, system views, and procedural language. For enterprises with existing MySQL systems, this compatibility capability can serve as an important technical foundation for database migration.
But compatibility doesn't mean all projects can migrate without code changes. The extent to which migration is possible still depends on which features the original system actually uses.
II. What exactly does MySQL compatibility include?
In my view, judging whether a database has sufficient MySQL compatibility cannot be done by just testing a few SELECT statements.
A business system that has run normally for years usually involves multiple database-level features.
Compatibility can be broken down into the following parts:
| Compatibility Dimension | Specific Content | Common Issues |
|---|---|---|
| Data types | INT, BIGINT, DECIMAL, DATETIME, JSON | Precision, range, conversion |
| SQL syntax | SELECT, INSERT, UPDATE, DELETE | Syntax differences |
| Built-in functions | Date, string, numeric functions | Different return results |
| Database objects | Views, indexes, triggers | Object creation failure |
| Procedural language | Stored procedures, functions, cursors | Variable and control statement differences |
| System views | INFORMATION_SCHEMA etc. | Metadata query inconsistency |
| Application interfaces | JDBC, connection pool, drivers | Type mapping, metadata differences |
| Runtime behavior | Transactions, locks, collation | Inconsistent execution results |
Here a concept needs distinguishing: syntax compatibility and behavior compatibility are not the same thing.
For example, two databases both support the following SQL:
SELECT *
FROM orders
WHERE create_time >= '2026-11-01'
ORDER BY create_time DESC
LIMIT 20;
This only means the statement can be parsed and executed.
If the two databases handle time types, time zones, string comparison, or collation differently, the final returned data may still be different.
So a valuable compatibility test cannot just check whether SQL reports errors; it also needs to verify execution results and business behavior.
III. Data type compatibility: the first thing to check during migration
Data types are a foundational part of MySQL migration.
Often, table structure conversion looks simple, but the value ranges, precision, and implicit conversion rules behind data types can directly affect business results.
1. Numeric types need attention to value ranges
For example, a common field definition in MySQL:
CREATE TABLE account (
id BIGINT NOT NULL AUTO_INCREMENT,
user_id BIGINT NOT NULL,
balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
status TINYINT NOT NULL DEFAULT 1,
PRIMARY KEY (id)
);
Several things are worth noting here:
- BIGINT is used for storing larger integers.
- DECIMAL is used for storing values with fixed precision.
- TINYINT is often used for status fields.
- AUTO_INCREMENT is used to automatically generate primary keys.
Especially DECIMAL, it is not recommended to casually change it to FLOAT or DOUBLE during migration.
For example, if the account amount is:
100000.35
Using a floating point type may cause precision issues.
For financial, order amount, and settlement data, you should prioritize preserving exact numeric types and verify precision, scale, and calculation results between source and target.
Additionally, MySQL's UNSIGNED type deserves special checking.
Unsigned integers have different value ranges than signed integers. If the field range changes after migration, original valid data may risk going out of bounds.
2. Date and time types cannot just look at field names
Common date and time types in MySQL include:
- DATE
- TIME
- DATETIME
- TIMESTAMP
- YEAR
Although these types have corresponding compatibility support in KingbaseES's MySQL mode, you still need to pay attention to precision, time zone, and value ranges when using them.
For example:
CREATE TABLE user_login (
id BIGINT NOT NULL AUTO_INCREMENT,
user_id BIGINT NOT NULL,
login_time DATETIME(6),
PRIMARY KEY (id)
);
Here microsecond precision is used.
If the application originally relies on microsecond-level time for sorting, you need to verify whether the target database preserves the same precision during migration.
Also, DATETIME and TIMESTAMP semantics cannot be simply considered the same. Especially for businesses involving different time zones, you need to verify the relationship between JDBC connection time zone, database time zone, and application time zone.
3. ENUM and SET types also need consideration
MySQL has two other distinctive data types:
CREATE TABLE user_profile (
id BIGINT PRIMARY KEY,
gender ENUM('UNKNOWN', 'MALE', 'FEMALE'),
tags SET('VIP', 'ACTIVE', 'NEW')
);
ENUM restricts which values a field can take, while SET stores multiple set members.
KingbaseES's MySQL compatibility documentation lists support for ENUM and SET types.
However, in actual migration, you still need to check whether the application depends on specific enum ordering, defaults, and string-to-number conversion behavior.
For newly developed business, a status dictionary table or explicit constraint design is sometimes more convenient for later maintenance.
IV. SQL syntax compatibility: can common statements be used directly?
SQL syntax is the compatibility issue developers most easily encounter.
KingbaseES's MySQL compatibility mode supports a large amount of common MySQL syntax, including DDL, DML, DQL, and some MySQL-specific statement forms.
Below are explanations using common business scenarios.
1. INSERT ON DUPLICATE KEY UPDATE
This is a common pattern in MySQL projects.
For example, user score table:
INSERT INTO user_score (
user_id,
score
)
VALUES (
10001,
200
)
ON DUPLICATE KEY UPDATE
score = score + 200;
If the user doesn't exist, a record is inserted; if there is a unique key conflict, the original score is updated.
This operation is often used in points accumulation, configuration synchronization, and business data updates.
KingbaseES's MySQL compatibility covers the INSERT ON DUPLICATE KEY clause.
During migration, special care must be taken to check whether unique constraints are correctly migrated, because this SQL's execution logic depends on unique key conflict detection.
2. REPLACE statement
MySQL has another special pattern:
REPLACE INTO user_config (
id,
config_value
)
VALUES (
10001,
'enabled'
);
REPLACE is not an ordinary UPDATE in the usual sense.
When a primary key or unique key conflict occurs, it may delete the old record and insert a new one.
This means if the table has associated foreign keys, triggers, or other dependencies, you need to pay special attention to behavior changes.
KingbaseES's MySQL compatibility documentation lists support for the REPLACE statement.
But migration testing cannot only verify that the statement succeeds; you also need to check associated data, trigger behavior, and the final record content.
3. INSERT IGNORE
For example:
INSERT IGNORE INTO user_score (
user_id,
score
)
VALUES (
10001,
200
);
This pattern is used in MySQL to ignore certain insert errors.
But IGNORE handling involves specific error types; it cannot be understood as all exceptions being safely ignorable.
If business relies on it to handle duplicate data, you need to test unique key conflicts, data conversion errors, and other constraint errors separately during migration.
4. LIMIT in UPDATE and DELETE
In MySQL business code, LIMIT is sometimes used to control the number of rows for batch updates and deletes.
For example:
DELETE FROM task_queue
WHERE status = 'DONE'
LIMIT 1000;
Or:
UPDATE task_queue
SET status = 'PROCESSING'
WHERE status = 'WAITING'
ORDER BY create_time
LIMIT 100;
KingbaseES's MySQL compatibility documentation includes support for LIMIT in UPDATE and DELETE.
This syntax is convenient for processing data in batches.
But in production, you still need to consider task execution order, concurrent claiming, and transaction boundaries. Especially when multiple worker threads process tasks simultaneously, you cannot rely on LIMIT alone to ensure tasks are not claimed twice.
V. Function compatibility: the details most easily hidden in business code
Compared to common SQL, function compatibility issues are often more easily overlooked.
Because developers may heavily use database functions in MyBatis XML, stored procedures, or views.
For example, date processing:
SELECT
DATE_FORMAT(create_time, '%Y-%m-%d')
FROM orders;
String processing:
SELECT
CONCAT(first_name, last_name)
FROM user_info;
And null handling:
SELECT
IFNULL(amount, 0)
FROM orders;
These functions are very common in MySQL projects.
KingbaseES's MySQL compatibility covers many common built-in functions, but the parameters, return types, and boundary behavior of different functions still need to be verified against the corresponding version's compatibility documentation.
Pay special attention to the following function categories:
| Function Category | Common Functions | Check Focus |
|---|---|---|
| Date functions | NOW, DATE_FORMAT | Time zone, format, precision |
| Character functions | CONCAT, SUBSTRING | Null handling, character length |
| Numeric functions | ROUND, CEIL | Precision, rounding mode |
| Null functions | IFNULL | Return type |
| Aggregate functions | COUNT, SUM | Null handling and numeric range |
| JSON functions | JSON_EXTRACT etc. | Path expression and return value |
Here is an example of a real problem.
Suppose the original system has a query that counts orders by month:
SELECT
DATE_FORMAT(create_time, '%Y-%m') AS month_value,
SUM(amount) AS total_amount
FROM orders
GROUP BY DATE_FORMAT(create_time, '%Y-%m');
During migration, you cannot just verify that it executes; you also need to check:
- Whether the date format is consistent.
- Whether the grouping results are consistent.
- How NULL data is handled.
- Whether SUM's return precision meets requirements.
- Whether time zone changes cause the statistical month to shift.
These issues may seem small, but if they occur in operational reports, they can lead to inconsistent statistics.
VI. CURRENT_TIMESTAMP auto-update time also needs separate verification
A table structure often seen in MySQL projects is:
CREATE TABLE product_info (
id BIGINT NOT NULL AUTO_INCREMENT,
product_name VARCHAR(100),
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
Here, update_time changes automatically when the record is updated.
If the application originally did not actively maintain this field in UPDATE statements, you must confirm whether the target end preserves the original behavior during migration.
KingbaseES's MySQL compatibility documentation lists compatibility support for the CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP table creation syntax.
But I recommend that actual testing should not just do a single INSERT.
You should separately verify:
- Whether INSERT auto-fills the time.
- Whether updating other fields updates the time.
- The behavior when UPDATE sets the same value.
- The behavior when explicitly passing the update time.
- The behavior for batch updates of multiple records.
Especially audit fields, order update time, and business status update time, these fields often participate in subsequent business logic.
VII. Besides SQL, system views and stored procedures cannot be missed
During database migration, one type of problem is not easy to spot: applications do not only execute business SQL; they may also directly query database metadata.
For example:
SELECT
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'orders';
Some general code generators, database management tools, and operation platforms rely on INFORMATION_SCHEMA to get table structures.
If the original application directly queries MySQL's system views, you need to confirm whether the target database provides corresponding compatible views, and whether field names, return types, and permissions match expectations.
KingbaseES's MySQL compatibility documentation includes compatibility content for multiple INFORMATION_SCHEMA views.
Besides metadata, stored procedures and triggers also need checking.
For example:
DELIMITER //
CREATE PROCEDURE update_order_status(
IN p_order_id BIGINT,
IN p_status VARCHAR(20)
)
BEGIN
UPDATE orders
SET status = p_status
WHERE id = p_order_id;
END //
DELIMITER ;
This code shows a common stored procedure definition in MySQL.
During migration, you need to check parameter definitions, variable declarations, condition handling, exception handling, cursors, and procedure invocation methods.
Especially for complex stored procedures, it is not recommended to rely solely on automatic conversion results.
You should break the procedure into several test scenarios and verify normal input, abnormal input, no-data, and multi-data cases separately.
VIII. How to actually migrate from MySQL to KingbaseES?
Compatibility capability determines which syntax and features the target database can recognize and process.
But actually completing migration requires a full implementation process.
Take an existing Java + MySQL order management system as an example.
The original system includes:
- User table.
- Order table.
- Product table.
- Order detail table.
- Payment record table.
- Multiple statistical views.
- Several stored procedures and scheduled tasks.
The goal is to migrate the database to CETC KingbaseES.
I recommend proceeding with the following flow:
MySQL source database
|
v
Database object scanning
|
v
Compatibility difference assessment
|
+---------+---------+
| |
v v
Compatible objects Objects to adapt
| |
| v
| SQL adjustments
| Object transformation
| |
+---------+---------+
|
v
Data structure migration
|
v
Full data migration
|
v
Data consistency verification
|
v
Application integration testing
|
v
Performance and stress testing
|
v
Production switchover
1. Do a compatibility assessment first
Don't directly migrate all data at the beginning.
First scan the source database, count tables, indexes, views, functions, stored procedures, and triggers.
CETC's KES DMS database migration assessment tool can be used for database compatibility assessment and migration work.
It is recommended to classify the assessment results into three categories:
| Category | Handling Method |
|---|---|
| Directly migratable | Proceed to structure and data migration |
| Needs adaptation | Modify objects or SQL and validate |
| Risky | Develop a separate transformation plan |
This way, you can identify the parts with large workload early in the project.
2. Configure the target database's MySQL compatibility environment
KingbaseES offers MySQL mode, and related compatibility capabilities are controlled through initialization parameters and other configuration.
Here it's especially important: MySQL mode is not about casually changing a parameter on any existing instance to guarantee all object compatibility.
You should create or configure the target environment according to the installation and initialization manual for the corresponding version, and verify character set, collation, case sensitivity strategy, and compatibility parameters.
Configuration methods may differ across versions; it is not recommended to directly copy parameter files from other versions.
3. Execute structure migration
After structure migration, don't rush to import all data.
You should check:
- Whether primary keys are correct.
- Whether unique constraints are complete.
- Whether indexes are created successfully.
- Whether field types match expectations.
- Whether default values are preserved.
- Whether views can be queried normally.
- Whether stored procedures can be compiled normally.
Especially for auto-increment fields, check the primary key generation mechanism and current sequence value after migration to prevent primary key conflicts in newly added data after migration.
4. Perform full and incremental data synchronization
If the system allows downtime for maintenance, you can complete the final data migration during a business write-stop window.
If the business cannot stop writing for a long time, you need to design a full migration + incremental synchronization plan.
For example:
MySQL source
|
v
Full migration
|
v
KingbaseES
^
|
Incremental sync
|
MySQL source
Here, incremental synchronization capabilities, supported data sources and targets, DDL handling scope, and consistency guarantees need to be confirmed based on the actual migration tool and version used.
Do not equate completing full data copy with finishing migration.
Before final switchover, confirm incremental data has caught up, and prepare business write-stop, data verification, switchover, and rollback plans.
5. Application connection and business verification
For Java projects, you also need to check JDBC driver, connection URL, connection pool, ORM framework, and database metadata access.
For example, the original project may directly use MySQL driver in the configuration file:
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/order_db
After migrating to KingbaseES, you need to adjust the driver class and connection URL according to the official driver documentation of the target version.
You cannot simply replace the URL and assume the entire application is adapted.
SQL in MyBatis, pagination plugins, primary key generation strategy, and database dialect also need checking.
IX. After migration, why might performance differ from before?
There is a problem worth discussing separately.
Database compatibility does not mean execution performance is completely identical.
The same SQL executed in two databases may use different execution plans.
For example:
SELECT
user_id,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM orders
WHERE create_time >= '2026-11-01'
GROUP BY user_id
ORDER BY total_amount DESC
LIMIT 100;
After migration, even if results are exactly the same, execution time may differ.
Possible reasons include:
- Different data statistics.
- Index design unsuitable for the target end.
- Changed data distribution.
- Different parallel execution strategies.
- Different memory parameter configurations.
- Different disk and hardware environments.
Therefore, after migration, you need to re-analyze execution plans.
For high-frequency SQL, record execution time, scanned rows, and execution plans before and after migration, and optimize indexes and SQL based on the actual target environment.
Do not directly copy all parameters from the source database to the target database.
Database kernels and resource management mechanisms differ, and parameter applicability may also differ.
X. Compatibility testing: scenarios worth focusing on
If project time is limited and you can't manually check all SQL, you can first build a test set around high-risk functions.
The following checklist can serve as a basic testing scope for migrating MySQL to KingbaseES.
| Test Category | Test Content |
|---|---|
| Data types | BIGINT, UNSIGNED, DECIMAL, DATETIME |
| Auto-increment mechanism | AUTO_INCREMENT, primary key generation |
| SQL syntax | LIMIT, REPLACE, ON DUPLICATE KEY |
| Functions | DATE_FORMAT, IFNULL, CONCAT |
| Strings | Case sensitivity, collation, character length |
| Date and time | Time zone, microseconds, auto-update time |
| Database objects | Views, triggers, indexes |
| Stored procedures | Parameters, variables, exception handling, cursors |
| Transactions | Commit, rollback, lock wait |
| Application | JDBC, MyBatis, connection pool |
| Performance | High concurrency, batch writes, complex queries |
During testing, it's best to prepare both normal data and boundary data.
For example:
- Maximum precision value of DECIMAL.
- Empty string and NULL.
- Non-ASCII characters.
- Date boundaries.
- Duplicate primary keys.
- Large batch INSERT.
- Concurrent UPDATE.
These scenarios are more likely to expose compatibility issues than simply executing a few ordinary SQL statements.
XI. MySQL compatibility is not the end of migration
Back to the MySQL compatibility capability of CETC KingbaseES.
From the official compatibility documentation, KingbaseES's MySQL mode covers data types, built-in functions, system views, SQL syntax, and procedural language, among others.
It also includes MySQL-specific features like ENUM, SET, BIT, REPLACE, INSERT ON DUPLICATE KEY, INSERT IGNORE, and some UPDATE/DELETE with LIMIT usage.
This means that when migrating an existing MySQL system, you don't necessarily need to rewrite all SQL into another style. For features already supported with compatibility, you can prioritize using the compatibility mode to reduce transformation workload.
However, I believe the real value of compatibility is not to let developers never modify code, but to give the migration process a more controllable starting point.
First use compatibility capability to retain original business logic, then gradually clean up legacy SQL, unify data types and database access standards, so it's easier to control project risk.
For long-running enterprise systems, migrating a database is not simply changing a connection address; it is an adjustment to the data infrastructure.
Preserve whatever business logic can be preserved, identify what needs modification early, and finally confirm results through data verification and business testing.
This is more practical than pursuing a one-time zero-modification migration.
Database compatibility should also be understood from this angle: it reduces the complexity of migration, not the need for migration verification itself.