Showing entries 1 to 10 of 15
5 Older Entries »
Displaying posts with tag: functions (reset)
Mysql: creating a link to your glossary while fetching text for a webpage

This MySql function will generate a link to your glossary when you are fetching text for a web page. All code is provided in a zip file.

MySql Lorum Ipsum generator

A short MySql function to generate a Lorum Ipsum text. You can download the code in the zip file below.

Howto generate meaningful test data using a MySQL function

You can use this MySQL function to generate names, (e-mail)addresses, phone numbers, urls, bit values, colors, IP address, etc.. As usual, the code is provided in a zip and the code is fully documented.

MySql: cleaning user input before storing the data

This article describes a single MySql function to sanitize strings. The provided options are:

  • replace multiple spaces to a single space
  • remove Unix and Windows linebreaks and replace them with a space
  • remove tabs and replace them for a single space
  • remove multiple backslashes
  • completely remove htmlentities (&)
  • replace htmlentities with the corresponding character
  • remove all HTML tags
Custom auto increment values

The auto_increment for MySQL fields are very useful but what about if I need a custom auto-increment like a custom format like 001-000033,

001-000034
001-000035
001-000036
...

To make it possible we have an option based on past article MySQL Sequences:

  1. Create the table to store the current sequence values:

    CREATE TABLE _sequence
    (
        seq_name VARCHAR(50) NOT NULL PRIMARY KEY,
        seq_group VARCHAR(10) NOT NULL,
        seq_val INT UNSIGNED NOT NULL
    );
    
  2. Create a function to get and increment the current value:

    delimiter //
    DROP FUNCTION IF EXISTS getNextCustomSeq//
    CREATE FUNCTION getNextCustomSeq
    (
        sSeqName VARCHAR(50),
        sSeqGroup VARCHAR(10)
    ) RETURNS VARCHAR(20)
    BEGIN
        DECLARE nLast_val INT; 
     
        SET nLast_val =  (SELECT seq_val …
[Read more]
A better way to get Primary Key columns

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



When an application asks MySQL for the Primary Key of a table, there are several ways to go about doing this. A fast way would be to use these statements:

DESCRIBE `dbName`.`tableName`;
-- or
SHOW INDEX FROM `dbName`.`tableName`
WHERE `Key_name` = 'PRIMARY';


The result set would have to be parsed in order to get the column names. This is not a recommended way to get the PK columns due to its limited usefulness as the column names cannot be returned INTO a variable.

Another method often used is this SELECT statement that uses a table JOIN:

SELECT k.`COLUMN_NAME`
FROM `information_schema`.`TABLE_CONSTRAINTS` t
JOIN `information_schema`.`KEY_COLUMN_USAGE` k
USING (`CONSTRAINT_NAME`, `TABLE_SCHEMA`, `TABLE_NAME`) …
[Read more]
Safe DML Options

Permalink: http://bit.ly/VNLTQe



The Safe DML project provides automatic creation of backups and an undo functionality for MySQL. These two abilities do not rely on the command line shell and can simply be executed by queries.

$DML() Options

Inside the stored procedure, $DML(), you can find the following options that can be set:

-- Switches logging on/off
DECLARE logging BOOLEAN DEFAULT FALSE;
-- Clears the logs per call
DECLARE clearLogs BOOLEAN DEFAULT TRUE;
-- Set to FALSE to backup only the current db in use
DECLARE backupAllDB BOOLEAN DEFAULT TRUE;
-- Disables filtering out of unsupported statements
DECLARE dmlFilter BOOLEAN DEFAULT TRUE;


  • The logging option enables/disables logs written by Safe DML into the `debug` table in …
[Read more]
Safe DML

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



There is no native undo ability inside MySQL. Thus, the common methods to workaround the problem is through creating backup dumps and enabling binary logging, using transactions, and requiring the WHERE clause in Data Manipulation Language commands by using the safe updates option. These methods have drawbacks:

  1. Creating backups via mysqldump and using binary logging to revert to a point in time will have the same effect as an undo functionality. However, these are executed via the command line shell. Since these tools are not executed inside MySQL, this method may not be convenient and presents limitations on when it can be used.
  2. Transactions allow you to "undo" as long as you have not committed your data manipulation changes. Imagine if you discover data manipulation changes that you wish to …
[Read more]
True Random Database and Table Name Generator - Part 2 of 2

Permalink: http://bit.ly/QuBLVB



Read part 1 for the rationale behind the code.

As discussed in the first part of this blog entry, we'll be utilizing a statement that uses base 36 to generate the random name. We will be adding the $ and _ characters using the ELT function. Here is a true random database and table name generator:

DELIMITER $$
DROP FUNCTION IF EXISTS `randomNameGenerator` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `randomNameGenerator`(
) RETURNS varchar(64) CHARSET utf8
BEGIN
DECLARE numberOfChars, charDiceRoll TINYINT(2);
DECLARE charCount TINYINT DEFAULT 0;
DECLARE randomChar CHAR(1);
DECLARE randomName VARCHAR(64) DEFAULT '';
[Read more]
True Random Database and Table Name Generator - Part 1 of 2

Permalink: http://bit.ly/UZY7xT



Skip to part 2 to go straight to the code snippet.

See also a similar generator: Truly Random and Complex Password Generator

Database names and table names have certain restrictions in MySQL:

  • The maximum name length for both are 64 characters
  • Allowed characters are a-z, A-Z, 0-9, $, and _


It is possible to create a table or database with a dot (.) in its name, however this is not recommended as it will cause some of MySQL's built-in functions to not work as …

[Read more]
Showing entries 1 to 10 of 15
5 Older Entries »