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:

For H2 1.4.x, please use 1.0.0 version as following:

<dependency>
    <groupId>org.mvnsearch</groupId>
    <artifactId>h2-functions-4-mysql</artifactId>
    <version>1.0.0</version>
</dependency>

For H2 2.0.x, please use 2.0.0 version as following:

<dependency>
    <groupId>org.mvnsearch</groupId>
    <artifactId>h2-functions-4-mysql</artifactId>
    <version>2.0.0</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);

• From H2 210, and you can use INIT parameter to load functions automatically.

String jdbcUrl = "jdbc:h2:mem:sample;INIT=RUNSCRIPT FROM 'classpath:db/migration/h2/V0_functions.sql'";
String jdbcUrl2 = "jdbc:h2:mem:sample;INIT=RUNSCRIPT FROM 'classpath:db/migration/h2/V0_functions.sql';RUNSCRIPT FROM 'classpath:your_script.sql'";

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

• H2 functions: http://www.h2database.com/html/functions.html
• MySQL functions: https://dev.mysql.com/doc/refman/8.0/en/func-op-summary-ref.html
• application-test.properties with H2 MySQL Mode support

### 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