Menu
I have a query that does anORDER BY on a VARCHAR column that contains email addresses.
If I hit my physical MySQL db, it ignores case in the ORDER BY. However, my h2 in-memory DB is respecting case. It is set to MySQL mode.
Anyone know why this is?
320
It can work as an in-memory database with a very satisfying performance result. H2 can be programmed to listen to a port, and it will expose the in-memory database to external applications for remotely accessing and browsing the database.
All database engines behave a little bit different. Where possible, H2 supports the ANSI SQL standard, and tries to be compatible to other databases. There are still a few differences however: In MySQL text columns are case insensitive by default, while in H2 they are case sensitive. However H2 supports case insensitive columns as well.
With this configuration, the data is not lost even after spring boot restart and computer restart. You would find H2 being very rarely used in this way. If you are really interested in a persistent database, we recommend exploring MySQL, Oracle or some other relational database.
I figured out that H2 supports a mode option that H2 uses to emulate behaviour of specific database. To run H2 in SQL Server mode you can use following JDBC URL. If you are using Spring Boot then you can specify using the following property. As you can see we have specify mode as MSSQLServer. With this mode, H2 emulates following features:
Case sensitivity when evaluating strings in databases is determined by the collation.
Check the collation handling on H2: http://www.h2database.com/html/grammar.html#set_collation
184
As an alternative to using a collation, you can disable case sensitivity using SET IGNORECASE TRUE. This needs to be done before creating the tables.
The reason why the MySQL mode of H2 isn't case insensitive is: compatibility modes in H2 don't affect how things are persisted (otherwise you couldn't access a database in a different compatibility mode later on, or disable the compatibility mode). Case sensitivity does affect how things are stored (specially indexes).
2
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
