Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

H2 In memory DB treating ORDER BY differently in MySQL mode

Tags:

java

mysql

sql-order-by

collation

h2

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?

like image 320
stevebot Avatar asked Jul 01 '11 19:07

stevebot

People also ask

Can H2 be used as an in-memory database?

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.

What is the difference between MySQL and H2?

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.

Is it possible to use H2 as a persistent database?

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.

How to run H2 in SQL Server mode?

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:

2 Answers

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

like image 184
OMG Ponies Avatar answered Oct 13 '22 09:10

OMG Ponies

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).

like image 2
Thomas Mueller Avatar answered Oct 13 '22 09:10

Thomas Mueller
Sign in to Comment

Related questions

Create quick/reliable benchmark with java?
builder.parse((new StringReader(xml)) returns DeferredDocumentImpl
Android - what is a foreground service? (vs regular service)
java: validating that all text fields in GUI are completed
Testing performance of Java project in Eclipse using VisualJVM
Java Swing revalidate() and repaint() problem
How do I mock a method inherited from an abstract class with EasyMock?
Memory leak in weakValue map reference for synchronized method
Correctly encoding characters in a URL when using HttpClient
Is there any #IF and #CONST .NET equivalent in Java/Android? [duplicate]
is there an overhead on setting a JVM to listen to debug port?
Swing Component - disabling resize in layout
Android Declare a Variable
java sample for encoding/decoding EXI? [closed]
Fetching All Items with DynamoDBMapper
Java Annotation C# equivalent [duplicate]
how to use log4j with multiple classes?
How to find a good/optimal dictionary for zlib 'setDictionary' when processing a given set of data?
Getting a list of active sessions in Tomcat using Java
Get a node's inner XML as String in Java DOM

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!