Skip to content

Tag Archives: function

Exiting a MySQL Stored Procedure in the middle of the code

Friday, December 17, 2010

While exiting a MySQL function is fairly straight-forward, simply use the RETURN keyword, exiting a Stored Procedure is not quite as obvious.  There is no EXIT keyword, however you can use the LEAVE keyword and specify a label that is associated with the BEGIN of the Stored Procedure.  In this way, you are defining the Stored Procedure with a Label, which the LEAVE statement can then act upon.

As an example, look at the following Stored Procedure :

CREATE PROCEDURE TestProc(Value INT)

ThisSP:BEGIN

IF  Value is null or Value=0 then
Select ‘Invalid Value’;
LEAVE ThisSP;
END IF;

Select * from Table twhere t.Value=Value;

END;

Filed in MySQL | Also tagged , , , , , , , , , , | Comments (2)

MySQL Collate and Case Sensitivity – Flexible Compare

Tuesday, August 31, 2010

In a typical MySQL installation, the default character set and collation are latin1 and latin1_swedish_ci.  This means that all string comparisons will be performed in a case insensitive manner.  So, ‘Search String’='search string’.  This is great for most WHERE expressions, however there are occasions where performing a case-sensitive search is required.  Luckily MySQL provides the collate clause which allows you to override whatever the default collation is for a comparison.   As an example :

select * from mytable where first_name collate latin1_general_cs=’damon’

This will only return rows will the first_name matches damon exactly. Damon will not match.

You can also use latin1_bin which performs an exact binary match.

If you are unsure of a field or strings collation, you can use the COLLATION(str) function to return the collation of the string argument.

Filed in MySQL | Also tagged , , , , , , , , , | Comments (0)

How do I store an IP Address in MySQL? INET_AtoN!

Thursday, August 19, 2010

One of the most popular questions out there is, “How do I store an IP Address in MySQL?”.   The fastest and most compact way to store an IP address in the format “10.51.1.10″ is as an unsigned integer.  If you store the actual values as a char(15), you will require 11 extra bytes per row.  In addition, using IP as a condition when querying is much faster when using an unsigned integer.  Testing has shown that range queries can result in as much as a 35 million times improvement over storing an IP address as a character string.  Yes, that is 35 million!

Simply define IP as an INT UNSIGNED.

Use MySQL’s built-in function INET_ATON() to convert the string representation of the IP Address into an unsigned Integer.  INET_NTOA() will perform the reverse translation.

This will result in space savings, and faster queries, who could ask for more than that?

Filed in MySQL | Also tagged , , , , , , , , , | Comments (0)

MySQL – DateTime vs. TimeStamp – When to use?

Saturday, June 12, 2010

This question often comes up, and while there is often a heated debate, these are my thoughts on the subject.

I typically use a timestamp when I need to record a fixed point in time. For example when a record was inserted into the database or when some useraction took place that resulted in a row being updated.   The Timestamp data type has various features that allow it to automatically perform this function.   The default value for the Timestamp data type results in it being set to the current_date when a new row is added.  If you as the On Update syntax, this value will also be updated whenever the row is updated.

I use a datetime field when the date/time can be set and changed arbitrarily. For example when the field can be updated based on specific events or actions, besides a simple row Update.  In addition, a TimeStamp field can only store dates since 1970, so if you need to store dates in the past, such as a birthday, you must use DateTime.

A couple other things to keep in mind, TimeStamp fields support the TimeZone setting on your server.  For example, if I have a database in Europe, and take a dump of that database to syncronize/populate a database in America, then the timestamp would update to reflect the real time of the event in the new time zone, while datetime would still reflect the time of the event in the European timezone.  By default, the current time zone for each connection is the server’s time, however the time zone can be set on a per-connection basis.

Lastly, TIMESTAMP stores its value in 4 bytes, while DATETIME uses 8 bytes.  This is the main reason why TimeStamp has a lower limit of 1970.

Filed in Uncategorized | Also tagged , , , , , , , , , , , , , , , , | Comments (0)