When you write stored procedures in MySQL or MariaDB, one of the missing features is an ARRAY type. But actually, it is possible to create arrays by using the JSON type. This article explains how to work with JSON arrays.
10 Older Entries »
MySQL and MariaDB don't allow optional parameters for stored procedures, nor procedures accepting any number of parameters. However, these features can be emulated by passing JSON documents. Let's see how.
The post Variable number of parameters and optional parameters in MySQL/MariaDB procedures appeared first on Federico Razzoli.
MySQL stored procedures, functions and triggers are tempting constructs for application developers. However, as I discovered, there can be an impact on database performance when using MySQL stored routines. Not being entirely sure of what I was seeing during a customer visit, I set out to create some simple tests to measure the impact of triggers on database performance. The outcome might surprise you.
Why stored routines are not optimal performance wise: short version
Recently, I worked with a customer to profile the performance of triggers and stored routines. What I’ve learned about stored routines: “dead” code (the code in a branch which will never run) can still significantly slow down the response time of a function/procedure/trigger. We will need to be careful to clean up what we do not need.
Profiling MySQL stored functions
Let’s compare these four simple stored functions (in MySQL 5.7): …[Read more]
With the changes to performance_schema in MySQL 5.7 Development
Milestone Release it is now possible to analyze and profile the
execution of stored programs. This is highly useful if you
develop more complex stored procedures and try to find the
bottlenecks. The "old" performance_schema up to MySQL 5.6 only
reported a CALL statement with a runtime, but no information on
statements that were executed WITHIN the stored procedure. Now
let's try this in the latest MySQL 5.7.6 DMR release. After
creating some test table and a test stored procedure we need to
activate the events_statements_history_long consumer, which is
OFF by default:
mysql> UPDATE setup_consumers SET ENABLED="YES"
WHERE NAME = "events_statements_history_long";
Then let's call the stored procedure that we want to inspect:
mysql> CALL …
The stripped down stored procedure shown below will accept any Data Manipulation Language statement as its parameter and automatically log the statement and create table backup copies before the statement is executed. The logging functionality is similar to MySQL's binary log but exclusive to DML statements and is useful for table data recovery operations, such as undoing the last table data change or to revert databases back to a certain point in time. All this is done exclusively using stored routines (procedures and functions).
Its assumed that the databases and tables that will be used are already formed to specific business requirements since DDL statements will not be logged by the stored procedure. Though logging of table data changes can also be achieved using triggers, it is not practical to alter each and …
A very old post of mine in 2009, MySQL’s stored procedure language could be so much more Useful suggested that it would be nice if MySQL could be adapted to use compound statements directly from the command line in a similar way to the language used for stored procedures. I’ve just seen that this seems to … Continue reading MariaDB 10.1 Brings Compound Statements
Shantanu asked a follow-up question on my Cleanup a MySQL Schema post from last month. He wanted to know if there was a way to capture foreign keys before removing them. The answer is yes, but how you do it depends on whether the primary key is based on a surrogate key using an auto incrementing sequence of a natural key using descriptive columns.
You can capture foreign keys with a simple query when they’re
determined by a single column value. However, this script creates
ALTER statements that will fail when a table holds a
multiple column foreign key value. The
statement would look like this when capturing all foreign key
values in a MySQL Server:
SELECT CONCAT('ALTER TABLE',' ',tc.table_schema,'.',tc.table_name,' ' ,'ADD CONSTRAINT',' …
As I review with my students, a stored function works like a
standalone program, while a stored procedure runs in the scope of
another program unit. For example, you can compare the result of
a function as an expression in an
IF add_numbers(1,3) > 3 THEN ... ELSE ... END IF;
You can’t call procedures inside an
but you can call the procedure and use a single
OUT-mode (pass-by-reference) parameter from
the procedure in a subsequent
IF statement. You can
implement a a wait procedure like that with the following
The example first creates two tables, the
-- Drop road_runner table if exists. DROP TABLE IF EXISTS road_runner; -- Create roadrunner table. …
My students required way to remove all their tables, views, and
constraints from a MySQL
database (or the alias
schema). Since they’re using referential or foreign
key constraints, I also wrote one procedure to drop all foreign
key constraints from a database. There’s also another to drop
views. The final stored procedure calls the procedure that drops
foreign keys, then calls the procedure to drop views before
dropping all the tables.
dropForeignKeys stored procedure:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69
-- Provide a log file debugging statement. SELECT 'DROP PROCEDURE IF EXISTS dropForeignKeys'; -- Conditionally drop the procedure. DROP PROCEDURE IF EXISTS …
10 Older Entries »