h2-functions-4-mysql
H2 User defined functions for MySQL.
Why this project?
I always use H2 for unit test instead of real MySQL(Docker), but some MySQL functions are not available in H2.
So I create this project to define some functions for MySQL and make unit test easy.
How to use?
In you pom.xml add following dependency:
<dependency >
<groupId >org.mvnsearch.mysql</groupId >
<artifactId >h2-functions-4-mysql</artifactId >
<version >1.0.0-SNAPSHOT</version >
</dependency >
Flyway with Spring Boot: automatically(classpath:db/migration/h2/V0_functions.sql), no configuration.
Manual: Add following code in your application. datasource is h2 datasource.
H2FunctionsLoader.loadMysqlFunctions(dataSource);
Function List
N: not available, Y: same, A: available
MySQL
H2
Status
ABS
ABS
Y
ACOS
ACOS
Y
ADDDATE
A
ADDTIME
A
AES_DECRYPT
A
AES_ENCRYPT
A
ANY_VALUE
ASCII
ASCII
Y
ASIN
ASIN
Y
ATAN
ATAN
Y
ATAN2
ATAN2
Y
AVG
AVG
Y
BENCHMARK
BIN
A
BIN_TO_UUID
BIT_AND
BIT_AND
Y
BIT_COUNT
N
BIT_LENGTH
BIT_LENGTH
Y
BIT_OR
BIT_OR
Y
BIT_XOR
CAST
CAST
Y
CEIL
CEIL
Y
CEILING
CEILING
Y
CHAR
CHAR
Y
CHAR_LENGTH
CHAR_LENGTH
Y
CHARACTER_LENGTH
CHARACTER_LENGTH
Y
CHARSET
A
COALESCE
COALESCE
Y
COERCIBILITY
COLLATION
COMPRESS
COMPRESS
Y
CONCAT
CONCAT
Y
CONCAT_WS
CONCAT_WS
Y
CONNECTION_ID
A
CONV
A
CONVERT
CONVERT
Y
CONVERT_TZ
A
COS
COS
Y
COT
COT
Y
COUNT
COUNT
Y
COUNT
COUNT
Y
CRC32
A
CREATE_DH_PARAMETERS
CREATE_DIGEST
A
CUME_DIST
CURDATE
CURDATE
Y
CURRENT_DATE
CURRENT_DATE
Y
CURRENT_ROLE
A
CURRENT_TIME
CURRENT_TIME
Y
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP
Y
CURRENT_USER
A
CURTIME
CURTIME
Y
DATABASE
DATABASE
Y
DATE
A
DATE_ADD
A
DATE_FORMAT
A
DATE_SUB
A
DATEDIFF
DATEDIFF
Y
DAY
DAY
Y
DAYNAME
DAYNAME
Y
DAYOFMONTH
DAYOFMONTH
Y
DAYOFWEEK
DAYOFWEEK
Y
DAYOFYEAR
DAYOFYEAR
Y
DECODE
DECODE
Y
DEFAULT
DEGREES
DEGREES
Y
DENSE_RANK
DES_DECRYPT
Deprecated
DES_ENCRYPT
Deprecated
ELT
ENCODE
Deprecated
ENCRYPT
ENCRYPT
Y
EXP
EXP
Y
EXPORT_SET
EXTRACT
EXTRACT
Y
ExtractValue
FIELD
A
FIND_IN_SET
A
FIRST_VALUE
FLOOR
FLOOR
Y
FORMAT
A
FOUND_ROWS
FROM_BASE64
A
FROM_DAYS
A
FROM_UNIXTIME
A
GeomCollection
GeometryCollection
GET_DD_COLUMN_PRIVILEGES
GET_DD_CREATE_OPTIONS
GET_DD_INDEX_SUB_PART_LENGTH
GET_FORMAT
GET_LOCK
GREATEST
GREATEST
Y
GROUP_CONCAT
GROUP_CONCAT
Y
GTID_SUBSET
GTID_SUBTRACT
HEX
A
HOUR
HOUR
Y
ICU_VERSION
IF
N
IFNULL
IFNULL
Y
INET_ATON
INET_NTOA
INET6_ATON
INET6_NTOA
INSERT
INSERT
Y
INSTR
INSTR
Y
INTERVAL
IS_FREE_LOCK
IS_IPV4
A
IS_IPV4_COMPAT
IS_IPV4_MAPPED
IS_IPV6
A
IS_USED_LOCK
IS_UUID
A
ISNULL
LAG
LAST_INSERT_ID
LAST_INSERT_ID
Y
LAST_VALUE
LCASE
LCASE
Y
LEAD
N
LEAST
LEAST
Y
LEFT
LEFT
Y
LENGTH
LENGTH
Y
LineString
LN
LN
Y
LOAD_FILE
LOCALTIME
A
LOCALTIMESTAMP
A
LOCATE
LOCATE
Y
LOG
LOG
Y
LOG10
LOG10
Y
LOG2
N
LOWER
LOWER
Y
LPAD
LPAD
Y
LTRIM
LTRIM
Y
MAKE_SET
MAKEDATE
A
MAKETIME
Y
MASTER_POS_WAIT
N
MAX
MAX
Y
MBRContains
N
MBRCoveredBy
N
MBRCovers
N
MBRDisjoint
N
MBREquals
N
MBRIntersects
N
MBROverlaps
N
MBRTouches
N
MBRWithin
N
MD5
A
MICROSECOND
A
MID
A
MIN
MIN
Y
MINUTE
MINUTE
Y
MOD
MOD
Y
MONTH
MONTH
Y
MONTHNAME
MONTHNAME
Y
MultiLineString
N
MultiPoint
MultiPolygon
NAME_CONST
NOW
A
NTH_VALUE
NTILE
NULLIF
NULLIF
Y
OCT
OCTET_LENGTH
OCTET_LENGTH
Y
ORD
A
PASSWORD
A
PERCENT_RANK
N
PERIOD_ADD
A
PERIOD_DIFF
A
PI
PI
Y
Point
Polygon
POSITION
POSITION
Y
POW
A
POWER
POWER
Y
QUARTER
QUARTER
Y
QUOTE
A
RADIANS
RADIANS
Y
RAND
RAND
Y
RANDOM_BYTES
A
RANK
REGEXP_INSTR
REGEXP_LIKE
REGEXP_LIKE
Y
REGEXP_REPLACE
REGEXP_REPLACE
Y
REGEXP_SUBSTR
RELEASE_ALL_LOCKS
RELEASE_LOCK
REPEAT
REPEAT
Y
REPLACE
REPLACE
Y
REVERSE
A
RIGHT
RIGHT
Y
ROLES_GRAPHML
ROUND
ROUND
Y
ROW_COUNT
ROW_NUMBER
RPAD
RPAD
Y
RTRIM
RTRIM
Y
SCHEMA
SCHEMA
Y
SEC_TO_TIME
A
SECOND
SECOND
Y
SESSION_USER
A
SHA1
A
SHA2
A
SIGN
SIGN
Y
SIN
SIN
Y
SLEEP
A
SOUNDEX
SOUNDEX
Y
SPACE
SPACE
Y
SQRT
SQRT
Y
STATEMENT_DIGEST
STATEMENT_DIGEST_TEXT
STD
STDDEV
STDDEV_POP
STDDEV_POP
Y
STDDEV_SAMP
STDDEV_SAMP
Y
STR_TO_DATE
A
STRCMP
A
SUBDATE
A
SUBSTR
SUBSTR
Y
SUBSTRING
SUBSTRING
Y
SUBSTRING_INDEX
A
SUBTIME
A
SUM
SUM
Y
SYSDATE
SYSDATE
Y
SYSTEM_USER
A
TAN
TAN
Y
TIME
A
TIME_FORMAT
A
TIME_TO_SEC
A
TIMEDIFF
A
TIMESTAMP
A
TIMESTAMPADD
TIMESTAMPDIFF
TO_BASE64
A
TO_DAYS
A
TO_SECONDS
A
TRIM
TRIM
Y
TRUNCATE
TRUNCATE
Y
UCASE
A
UNCOMPRESS
UNCOMPRESSED_LENGTH
UNHEX
A
UNIX_TIMESTAMP
A
UpdateXML
UPPER
UPPER
Y
USER
USER
Y
UTC_DATE
A
UTC_TIME
A
UTC_TIMESTAMP
A
UUID
UUID
A
UUID_SHORT
A
UUID_TO_BIN
VALIDATE_PASSWORD_STRENGTH
VALUES
VAR_POP
VAR_POP
Y
VAR_SAMP
VAR_SAMP
Y
VARIANCE
VERSION
A
WAIT_FOR_EXECUTED_GTID_SET
WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS
WEEK
WEEK
Y
WEEKDAY
A
WEEKOFYEAR
A
WEIGHT_STRING
YEAR
YEAR
Y
YEARWEEK
A
References
# ## datasource
spring.datasource.driver-class-name =org.h2.Driver
spring.datasource.url =jdbc:h2:mem:public; MODE=MYSQL;DATABASE_TO_UPPER=false;INIT=CREATE SCHEMA IF NOT EXISTS public
spring.datasource.username =sa