Case sensitive matches in MySQL

This is an interesting tidbit that I came across in my aimless wanderings around on the Internet. Unfortunately I don’t remember the place that I found it – it was during a random Google search that I had made buried in the second page somewhere.

So the requirement was to do a case sensitive search for values in a MySQL table. So if you wanted to search for records that contained the term ‘foo’ in the title from a table ‘FooName’ containing the following records –

FooName

Title	Description
foobar	a bar of foo
Foo	royal foo
Fooka	car of foo
foo	just foo

The typical select query –

Select Title from FooName where Title like '%foo%'

Returns

foobar
Foo
Fooka
foo

This may not be exactly what you wanted since ‘foo’ is all in lowercase and the query above returns all values that have the letters ‘foo’ in that order regardless of case. In order to ensure case sensitivity in MySQL just modify your query like so –

Select Title from FooName where binary Title like '%foo%'

Returns

foobar
foo

Advertisement

MySQL Resources

One of the things I have discovered the hard way about working with MySQL is that unlike SQL Server 2005 resources for MySQL are not as widespread. Other than the official MySQL website and the MySQL sponsored forums there seem to be a strange lack of technical resources on MySQL. Since there seem to be so little I thought it mights be beneficial –

• MySQL Reference – The online link to MySQL 5.0 is here. It is good reference but is a little skimpy on the examples unless you dig into the comments section at the bottom of the pages.
• Xaprb – This is one I am a big fan of – the blog is focussed on MySQL. The author really knows his stuff, not only in MySQL esoterica but about SQL in general. Great place to go to learn more about MySQL.
• MySQL Performance Blog – This is another blog written by MySQL consultants based in Europe. This blog is focussed more on the performance tuning tips for MySQL. Great resource to learn the various settings and variables in MySQL that can be used to diagnose and tweak MySQL performance.
• MySQL Forge – This is MySQL’s version of Sourceforge.com. This is a community site for MySQL tools and scripts. Lots of perl, php script tools dealing with various aspects of database maintenance and functionality.
• MySQL – The definitive guide to using, programming and administering MySQL 4.1 and 5.0 (ISBN: 0672326736) – This book by Paul Dubois is a great reference for MySQL. As befits a definitive guide the book is hefty at nearly 1300 pages but a must have for any in depth study of the database.
• mySQL CheatSheet – There is a neatly laid out cheat sheet in this blog post. I find it useful lying on my desk for the times I blank out on stuff.
• There are a couple of blogs by mySQL insiders that I keep an eye on – http://jan.kneschke.de/projects/mysql and http://www.planetmysql.org/robin/

Thats about it for now. Please feel free to add ones you guys go to in the comments to the post (the comments are moderated so they may not appear as soon as you insert them) :-)

Getting Microsoft SQL Server and mySQL to talk

One of the biggest things I have to do as part of my company’s transition to using mySQL is the migration of the existing data to the mySQL platform. The problem was getting Microsoft’s SQL Server 2005 and mySQL to talk in a straightforward and flexible manner. The main issue is that SQL Server 2005 does not provide ODBC drivers out of the box.

This IMO is a big backward step for SQL Server 2005. If you use the SQL Server Management Studio interface there is .NET ODBC driver available as a data-source when trying to import data but when you try to make ODBC as your data-sink these options are no longer available. You get this issue when using SSIS also so I went on another tack – linked servers.

Linked servers are simply connections to other data sources or data sinks that are maintained by the Microsoft SQL Server. These can be local or remote and are a flexible and powerful way of making data that is spread across multiple servers available. You big advantage is that you could address the data with SQL statements directly as if they were on the same machine. Linked Servers could be ODBC data-sources as long as the appropriate ODBC drivers were available.

So I went to mySQL and got the mySQL ODBC Driver. After installing the driver on the server (mySQL has detailed installation instructions here), setting up the linked server was pretty straightforward –


EXEC master.dbo.sp_addlinkedserver @server = N'MYSQL', @srvproduct=N'MySQL', @provider=N'MSDASQL', @provstr=N'DRIVER={MySQL ODBC 3.51 Driver}; SERVER=127.0.0.1; DATABASE=DB_Name; USER=UserName; PASSWORD=Password; OPTION=3'


Now you have set up a linked mySQL server using ODBC. Since this is an ODBC connection selecting and inserting data to and from it is slightly different. You use the openquery function like so –

Selecting Data -
select * from openquery(MySQL, 'select * from `db_name`.`table_name`')
Inserting Data -
INSERT into openquery(MySQL,'select * from db_name.table_name')
select * from db_name.dbo.table_name


mySQL Tools

Coming from a cocooned world of Microsoft development where the SQL Server Management Studio pretty much handled the spectrum of database administration and management – my immediate focus was to find similar tools for my SQL.
The surprising thing I found was that while GUI tools on Windows for mySQL were plentiful – GUI tools for mySQL on the Linux environment were scarce. On further reflection the assumption that there would be GUI tools on Linux shows considerable naiveté’ on my part. After all, if you are using the Linux environment on a day to day basis you would probably be looking for command line and shell scripting tools rather than GUI tools.
mySQL has adopted the approach of exposing an api for 3rd party tool builders. They have built a set of pretty basic tools that I talked about earlier but nothing that performed from end to end.
Once I let go of the approach of looking for 1 tool that did everything, however, I found better success. While still meager on the Linux side – there are some pretty useful tools for mySQL in the windows world .
My picks for desktop based tools are :

1. SQLYOG :- This tool is the product of a small Indian software company. However it has a decent Query interface, data migration tools as well as good schema management tools. The price is a perfectly affordable $70 for the enterprise version. The only quibble I have with this tool is that it seems to use up a lot of CPU and memory.
2. Toad for mySQL :- TOAD doesn’t need much introduction to database administrators. It is pretty much the de-facto tool that is used for Oracle. They have built a free-ware version of their tool which is pretty compelling. Admittedly it is lighter on features and does not seem to be able to browse data without throwing up errors but this would be a good solution to those that are used to TOAD and thus can avoid too large a learning curve. And last but not the least – its freeware.
3. DeZign for Databases :- This tool was recommended by a colleague (thanks Scott) as great database modeling tool. The trial version is hobbled in that all table are limited to a maximum of 8 columns but the tool IMO is well worth the investment. This is especially true if you need to keep updated ER Diagrams of all your databases and synchronize both from and to the database is needed.

There are a few web-based tools worth looking into also :

1. PHP MyAdmin :- This is the one that is touted by most everyone who has worked with mySQL. It is cross-platform, easy to set up on central location, easily accessed by anyone with an internet connection and is open source. The main complaint I have with this tool is that it is web based and therefore cannot handle tasks that require significant amounts of time. This effectively eliminates it IMO as a tool that can be used for an enterprise level database with significantly large amounts of data. It is a great tool, however, for small databases that require a limited amount of tuning. I also find the interface a bit confusing and a little dated – but thats mostly the web developer in me talking :-)
2. MonYog :- To be honest I haven’t really used this this tool very much. It is a beta product distributed freely by the makers of SQLYOG. It is an AJAX enabled tool used for monitoring the mySQL database.

mySQL Delimiters

OK – So I am writing my first stored procedure in mySQL (Yes – stored procedure and function support is available in mySQL 5 – look under stored routines :-)) and I kept getting this weird syntax error – Error Code : 1064 You have have an error in your SQL syntax;.

It was one of those frustrating problems – I tried everything I knew and I got nowhere. I knew that the error was wrong because I could execute the SQL statements outside the store procedure but I had no idea what the real problem was…

Finally, a good Samaritan in the form of a friend (John) of my friend Steven told me about the delimiter command. In normal cases the mySQL client interprets the ; character as the end of a SQL statement. But in the case when you are creating a stored procedure there are 2 sets of SQL for the client to deal with – one being the SQL to define the procedure itself CREATE PROCEDURE name (parameter1,..)BEGIN .. END; and the next level is the SQL in the stored procedure. If there are multiple SQL statements in the stored procedure then the client considers the ; at the end to be the close of the SQL for defining the stored procedure itself and tries to execute it and trips up… The solution is to define a different delimiter for the SQL in the stored procedure definition using the delimiter command before beginning the stored procedure definition. You must remember to revert the delimiter back to ; at the end – eg :-

delimiter //
drop procedure if exists `database``stored procedure` //
create procedure `database``stored procedure`
(parameter1 datatype,
parameter2 datatype)
begin
declare localvariable datatype;
set localvariable = parameter2;
select fields from table where field1 = parameter1;
end //
delimiter ;


The ironic thing of course is that once I realized what was happening I found mention of it in the reference manual here :-). Another thing that is mentioned that \ should not be used as a delimiter since it is an escape character in mySQL.

Nikhil in mySQL Land

It was decided recently, at work that we should standardize on mySQL on Linux as our enterprise database. I was asked to learn mySQL – pronto and take up the task of designing our new enterprise db platform and to move our schemas and data from our existing platform – MS-SQL 2005 to mySQL.

My initial impression of mySQL was that of a typical enterprise consultant – mySQL is an immature database – no stored procedures and triggers – heck it didn’t even have transaction support. As far as tools are concerned – fuhgedaboutit – I was a pampered MSSQL 2005 user with GUI tools for everything – what could mySQL have that even came close? mySQL on the face of it looked like a quirky and flawed database choice.

But, I got my marching orders and I began looking at everything mySQL related. Imagine my surprise when I found that mySQL has not only transactions but also stored procedures and triggers and a bunch of other features as well. Its feature set has really grown with the release of version 5.0 and its tool support is pretty good as well. In addition to the application and the tools there was an active community and decent documentation (though not quite in the same league as MSDN :-))

With this encouragement I decided to forge ahead and I installed mySQL 5.1 Beta (bleeding edge baby!) and mySQL GUI on my laptop – running Windows XP Professional. The installation was straightforward and I got in addition to the database – the following tools –
1. mySQL System Tray Monitor
2. mySQL Administrator
3. mySQL Query Browser
4. mySQL Migration Toolkit
5. mySQL Workbench
6. mySQL Instance Configuration Wizard
7. mySQL Command Line Client