How to support both MySQL and SAP HANA during development phase
Have you ever experienced a project that require support both MySQL and SAP HANA during the development phase.
In this blog post, I’d like to share my experience of how to support MySQL and SAP HANA during the development phase.
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.