If We want to develop applications that support both MySQL and SAP HANA at the same time. We have to find the difference between them.
I will compare the differences from below three points.
1. Primary key generation
2. Data type
Primary key generation
MySQL must have a primary key (PRIMARY KEY) when building a table, and each primary key content must be unique as a unique identifier for the piece of data in the table. At the same time, the primary key is often given an “auto_increment” attribute, so that each record in the field of the primary key is incremented by “1”.
SAP HANA doesn’t have this “auto_increment” attribute, so it can’t define a self-increment primary key in a table like MySQL. However, the sequence in SAP HANA (SEQUENCE) can indirectly achieve the role of the self-increment primary key.
CREATE TABLE `USER` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `created_date` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
SAP HANA implement:
CREATE COLUMN TABLE "USER" ( "ID" BIGINT CS_FIXED GENERATED BY DEFAULT AS IDENTITY NOT NULL , "NAME" NVARCHAR(200), "CREATED_DATE" LONGDATE CS_LONGDATE, PRIMARY KEY ("ID")) UNLOAD PRIORITY 5 AUTO MERGE ; CREATE SEQUENCE "USER_SEQUENCE";
JAVA implement sequence:
@Id @GeneratedValue(strategy = GenerationType.AUTO, generator = "sequence_generator") @SequenceGenerator(name = "sequence_generator", initialValue = 1, allocationSize = 1, sequenceName = "USER_SEQUENCE") private Long id;
For data type I just compare some frequently-used ones like below picture.
More Info please refer to Data mappings
For Function I also just compare some frequently-used ones, please see below items.
1. Globally unique identifier
-- MySQL uuid() -- SAP HANA select SYSUUID from dummy;
-- MySQL select mid('abcdefg',2,3); select substring('abcdefg',2); select substring('abcdefg' from 2); -- SAP HANA select left（’abcdefg’, ‘5’） from dummy; select right（’abcdefg’, ‘5’） from dummy;
3. Before or after the current day
-- MySQL -- The day before today SELECT DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 DAY),'%Y-%m-%d'); -- The day after today SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 1 DAY),'%Y-%m-%d'); -- SAP HANA -- The day before today SELECT ADD_DAYS(CURRENT_TIMESTAMP,-1) from dummy -- The day after today SELECT ADD_DAYS(CURRENT_TIMESTAMP,1) from dummy
4. GROUP BY
-- MYSQL -- Mysql group by statement can select fields that are not grouped select id,name,age from A group by age -- SAP HANA -- HANA group by statement can't select fields that are not grouped select id,name,age from A group by id
5. Data type casting
-- MYSQL -- CAST select * from dummy where is_deleted=0 order by case when dummy.status='Draft' then cast(1 as signed) when dummy.status='WaitApproval' then cast(2 as signed) when dummy.status='Reject' then cast(3 as signed) when dummy.status='Approve' then cast(4 as signed) else cast(5 as signed) end asc -- SAP HANA -- CAST select * from dummy where is_deleted=0 order by case when dummy.status='Draft' then cast(1 as integer) when dummy.status='WaitApproval' then cast(2 as integer) when dummy.status='Reject' then cast(3 as integer) when dummy.status='Approve' then cast(4 as integer) else cast(5 as integer) end asc -- MYSQL -- CONVERT convert(filed_name, data_type) -- SAP HANA -- NO CONVERT method
More information about the difference of the data types and functions, please email to me. I have prepare a document.
After mastered above knowledge. I believe we can easily develop applications that support both MySQL and SAP HANA at the same time.
Finally, Thank you for your reading. Any comments are welcome.