H2 User defined functions for MySQL.
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.
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'";
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 |
- 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