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 […]
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 […]
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 […]
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 […]
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 […]
A lot of places I saw people asking for ways to generate Insert statements. We do have GUI Tools which can extract insert statements for us readily. of the time I choose the MySQLDump way to generate insert statements. mysqldump -uroot -ppassword –complete-insert –no-create-info DATABASE TABLENAME > TABLENAME.sql But mind is very unstable and hungry, […]
The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. The file name must be given as a literal string. I have already written the basic how to follow that considering additional cases. A lot of questions I have seen for loading separated data to MySQL, […]
Assume we’ve a table with 100 rows and we need to select all columns but one. The problem is headache of actually typing out all 99 required columns!! Solutions / Ideas to above problem are: Ignorance is bliss. Select all(*) and ignore the column. Manually type column names or manage it with copy paste! Create […]
The Mysqldumpsplitter can extract database, table, all databases, all tables or tables matching on regular expression from a mysqldump. It can produce the compressed output of sqls in specified directories. The tool has undergone a complete rewrite, and there are many more functionalities added. Please follow GitHub Repository for future changes or further improvements. Download […]
Here is one more procedure – (this time) for mass renaming of table. Adding and Removing table-name prefixes 🙂 A friend of mine was renaming 100+ tables by using replace methods in notepad. I showed em a bit better editor: Editplus and then I thought of rescue rest of those who are still interested in […]
Change Is Inevitable