MySQL-Scripts

MySQL related blog posts with scripts

Providing Friday Proofs to MySQL Consultants

In the MySQL world, I am surrounded by some of the best database consultant, it brings them immense joy about having a “Friday”, so much so that they cannot believe that it actually is a Friday. Regardless of what they see on their watches and calendars, it becomes a crucial task of the day to prove it. Prove it technically that it actually is a Friday. This blog post is my attempt to share the proofs of existence of a “Friday”. Using MySQL datetime function: dayname Running system command from MySQL prompt

MySQL function returns dates of specified day & between date range

This mysql stored function will return you all the dates between given dates having specified day. In short if you say: give me all Sundays in this month. I have following for you: mysql> select Give_Me_Dates_Days(1,’2012-09-01′,’2012-10-01′) as All_Sundays; +——————————————————–+ | All_Sundays | +——————————————————–+ | 2012-09-02,2012-09-09,2012-09-16,2012-09-23,2012-09-30 | +——————————————————–+ The function parameters are as follows: Give_Me_Dates_Days(DAY_IDENTIFIER,START_DATE,END_DATE) Sunday = 1 (and rest you can figure-out).

Generate random test data for MySQL using routines

At times you’ll find yourself responsible for generating test data for newly created tables for testing or sampling purpose. There are tools that will generate random data for you but they’re not free. At-times you’ll write scripts to generate data but those will be table specific. I hate generating dummy data, yes I do and I assume you do too! I think that’s the major reason I wrote these MySQL functions and procedures for Generating dummy test data. GitHub: mysql random data generator.

MySQL Binary logs Generated Per Hour | Growth Estimate

The MySQL binary log is a set of log files that contain information about data modifications made to a MySQL server instance. At times we need to understand the disk usage by binary logs to know how much data is being written and probably growth estimation. Following command (shell script) will provide you with the hourly binlogs generated in MB. Note that the binary log name needs to be updated, I have used mysql-bin. Binary log growth per hour Assuming you have to collect the binary log growth from multiple servers we can use following script to fetch the data...

MySQL Function to Convert Date To Words

Recently I saw a MySQL Stored Function requirement on Experts-Exchange for converting date into some specific words format. You may find MySQL function for date to words conversion online; even udfs might be ready, but I decided to write my own. I wrote this simple function mainly based on SELECT CASE to convert dates in to words as follows: mysql>SELECT date_to_words(‘2010-05-08’); Eighth Day of May Two Thousand Ten Download sql file below the code. DELIMITER $$ DROP FUNCTION IF EXISTS `date_to_words` $$ CREATE FUNCTION `date_to_words` (mydate DATE) RETURNS VARCHAR(100) DETERMINISTIC BEGIN /* Converts date into words */ DECLARE yr INT;...