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.
5 Older Entries »
A short MySql function to generate a Lorum Ipsum text. You can download the code in the zip file below.
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.
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
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:
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 );
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 …
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:
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`[Read more]
FROM `information_schema`.`TABLE_CONSTRAINTS` t
JOIN `information_schema`.`KEY_COLUMN_USAGE` k
USING (`CONSTRAINT_NAME`, `TABLE_SCHEMA`, `TABLE_NAME`) …
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.
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 …
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:
- 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.
- 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 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 $$[Read more]
DROP FUNCTION IF EXISTS `randomNameGenerator` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `randomNameGenerator`(
) RETURNS varchar(64) CHARSET utf8
DECLARE numberOfChars, charDiceRoll TINYINT(2);
DECLARE charCount TINYINT DEFAULT 0;
DECLARE randomChar CHAR(1);
DECLARE randomName VARCHAR(64) DEFAULT '';
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 …
5 Older Entries »