Permalink: http://bit.ly/1kBCcQu



One of the sorely wanted features missing in MySQL is the ability to disable/enable triggers on the fly. By comparison, disabling/enabling Foreign Key constraints can be simply done by setting a server system variable:

SET FOREIGN_KEY_CHECKS = [TRUE|FALSE];

Now as of version 5.6, there is no built-in server system variable TRIGGER_CHECKS in MySQL. A simple workaround is to instead use a user-defined session variable. The setting for trigger checks stored into the session variable allows the setting to be seen by all statements, including all stored procedures and functions, as long as the user is connected, which for this workaround is in effect similar to using a server system variable.

Besides a session variable that switches the checks for all triggers, to …

Permalink: http://bit.ly/1pl6kz5



This is the workaround to create dynamic cursors in MySQL and is meant to be used together with the general purpose dynamic cursor stored procedure.

Increase the compared value of colValN in the WHILE-DO construct to the maximum number of columns your dynamic cursor will be needing. This fixes MySQL's limitation on cursors by providing the dynamic cursor with consistent column names that are independent of the underlying SELECT statement and also provide a predictable number of columns.

The final result is that the temporarily created table will be constant, while the records that the cursor will traverse will remain dynamic. The code in …

Permalink: http://bit.ly/1jxurF0



See part 1 for the rationale behind the code and part 2 to understand limitations faced by cursors in MySQL.

The code snippet below shows an example that performs an action with the cursor data as well as a good way to debug the stored procedure. This iteration also works around MySQL's cursor problem mentioned in part 2 by calling the dynamic cursor fix stored …

Permalink: http://bit.ly/RcRieg



Refer to part 1 for the rationale behind the code or you can skip to part 3 for a working example as well as how you can debug the stored procedure.

Important: The SP will create a table named `dynamic_cursor`. Make sure this table does not exist in the database where you will be storing the procedure. Here's the 1st iteration of a general purpose dynamic cursor:

DELIMITER $$
DROP PROCEDURE IF EXISTS `dynamicCursor` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `dynamicCursor`(
 IN selectStmt TEXT,
 IN whatAction VARCHAR(255),
 INOUT …

Permalink: http://bit.ly/TBKQL3



Skip to part 2 for the code snippet or to part 3 for a working example and how to debug the stored procedure.

Also another workaround on a missing MySQL functionality, enabling/disabling triggers, can be found here.

As of version 5.5, MySQL still does not have the native ability to execute a dynamic cursor. This can be worked around but the resulting stored procedure will have a few limitations.

This stored procedure is a general purpose …

There was a discussion on LinkedIn one month ago that caught my eye:

Database search by "within x number of miles" radius?

Anyone out there created a zipcode database and created a "search within x numer of miles" function ?
Thankful for any tips you can throw my way..

J

A few people commented that some solutions wouldn't scale. To understand why these sorts of geographic search queries are problematic in MySQL, it's best to show some execution plans on dummy data:

PLAIN TEXT SQL:

1. EXPLAIN …