Ask The Real Tom

February 19, 2010



MySQL Dynamic DDL in Prozedure or Function

Filed under: mysql — Tags: , , , — admin @ 10:16 am

Yesterday I ran into a challenge on mysql.

A procedure to create an drop a database on mysql has been needed. My first thought was “easy”, it wasn’t.

There is no “execute immediate” or dynamic variable replacement in mysql. The workaround to execute dynamic DDL in a mysql procedure was found in a bug description. (http://bugs.mysql.com/bug.php?id=9150)
It can be solved with a prepared statement.


set @s = concat('CREATE DATABASE ', p_schema_name);
prepare stmt from @s;
execute stmt;
deallocate prepare stmt;

My template to create a DB I just poste here. Maybe it helps:


delimiter //

DROP PROCEDURE IF EXISTS create_schema;
CREATE PROCEDURE create_schema (p_schema_name VARCHAR(50))
SQL SECURITY DEFINER
BEGIN
DECLARE v_schema_count INTEGER;
startb:BEGIN

-- check exists
SELECT count(1) INTO v_schema_count FROM information_schema.SCHEMATA WHERE upper(SCHEMA_NAME)=upper(p_schema_name);
IF v_schema_count > 0 THEN
SELECT 'Error: DB Exists' FROM dual;
leave startb;
END IF;
-- check name
IF ( INSTR(p_schema_name ,'AD_') = 0) THEN
SELECT 'Error: Invalid schema name' FROM dual;
leave startb;
END IF;
-- create db
set @s = concat('CREATE DATABASE ', p_schema_name);
prepare stmt from @s;
execute stmt;
deallocate prepare stmt;

-- grant permissiosn
set @s = concat('CREATE DATABASE ', p_schema_name);
prepare stmt from @s;
execute stmt;
deallocate prepare stmt;

END startb;
END;
//

April 1, 2009



Fast import/export in MySQL with LOAD DATA INFILE

Filed under: mysql — Tags: , , , , — admin @ 3:05 pm

MySQL does not offer many possibilities for importing/exporting data in a fast manner. One way to do so is to use the LOAD DATA INFILE statement for loading data from/to text files.

SELECT * INTO OUTFILE '/tmp/data.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; FROM test.data_table;

With the example above you create a csv (comma-separated values) file with the content of a given table.

To read rows from a text file into a table use the following example:

LOAD DATA INFILE '/tmp/data.txt' INTO TABLE data_table FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

Furthermore this way you are able to parallelize imports and exports to reduce the runtime. Just execute several statements simultaneously.

Powered by WordPress