Why MySQL is rubbish

I've been doing some work recently on migrating a CRM system from Salesforce to SugarCRM. Painful.

Both use MySQL I believe.

In migrating from Salesforce to SugarCRM I've exported the entire accounts table to CSV format including the ID field. Many many hundreds of rows.

Importing these to SugarCRM causes a duplicate index error. This is because the ID fields in Salesforce are designed to be case sensitive and are exported for example as 123A, 123a and so on. The letter "A" is not the same as the letter "a" - each have a different set of bits identifying them. However unless MySQL is set up to understand the difference at the bit level then these 2 letters are equal even in Indexes! Unbelievably this is the default behaviour. I am shocked.

So imagine I have 2 tables with alphanumeric codes identifying values within the table. I could accidentally join ABX123 to abx123 or any other mix of cases when joining the tables... that could be a disaster. So beware, if you use MySQL this is something to consider before blindly doing table joins in the database. In my opinion a good reason to avoid MySQL and use a decent database!

I am hoping I can get around this in the SugarCRM implementation by redefining the ID column. Fingers crossed.