The MySQL UNIQUE constraint is often used in a column definition in which we need each value for that column to be distinct from the others. Perhaps it is an email column for an on-line registration form and we want to ensure that users cannot register twice for an account using the same email. Whatever the case may be, UNIQUE is there to help us ensure this type of data integrity or business requirement. What if the target table already exists and you determine you need to add a UNIQUE constraint to an existing column? In this post, I will cover 2 ways you can implement a UNIQUE constraint on existing columns using the phpMyAdmin web interface…

Photo by Kaleidico on …

I recently watched a fantastic Python Pandas library tutorial series on YouTube. Without a doubt, Pandas is great for all sorts of data stuff. On the same token, MySQL Shell in Python mode is quite powerful in the sense that Python and the MySQL Shell (version >= 8.0) are somewhat united in the same environment. Although Pandas is in a league all its own when it comes to data analysis, between the power of MySQL and Python, we can also perform some basic analysis easily in MySQL Shell Python mode. In this blog post, I will cover some basic data analysis using Python mode in the MySQL Shell. Continue reading to see examples…

Business vector created by freepik – www.freepik.com

OS, Software, and DB used:

• OpenSuse Leap …

The MySQL DROP statement is one of many powerful DDL commands. Be it ALTER TABLE some_table DROP some_column or DROP some_table, this type of command can drastically change your data landscape because in executing MySQL DROP, you are completely removing objects from the database! If you are using the phpMyAdmin web interface, you can execute the MySQL DROP statement with just a few mouse clicks. Continue reading to see how…

Drops of water.

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

Note: The …

Over the last few months, I have written numerous blog posts on different features of the MySQL Shell ranging from basic CRUD to aggregate functions and DDL. As a part of the MySQL version 8 release, MySQL Shell is a powerful and alternative environment that you can manage and work with your data in using a choice of 3 languages: Python, Javascript, or SQL. So this blog post is a simple compilation of all the Python mode related posts, in one easy-to-access location…

Photo by Tamara Gore on Unsplash

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit …

This is the announcement blogpost and this is my overview taken from medium:

The one where MySQL 8.0 lands on Google Cloud SQL

Ada Doglace and Lily Grace (lilygrams). Photo by Anthony Ferrara.

There are many things that makes me happy. Puppies (see picture), food, wine and databases… (not particularly in that order). And things that makes me even happier such as a well designed schema and proper usage of ORM (Object Relational Mapping).

MySQL was the database I used to love to hate. It grew on me and the fact that long strides were made to make it more consistent and more modern kept me away from …

Using the MySQL ALTER TABLE command, you can easily change an existing columns’ name and datatype. With just a few clicks, you can do the same in the phpMyAdmin visual web interface. For many developers, this interface is the one they lean on most while programming so it can’t hurt to know how to do it yourself should you find yourself programming in this environment…

Self-Promotion

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

Suppose we have a table with these 3 columns:

• id
• title
• grade

Our goal is to rename the title column to paper_title and to also change the datatype …

We all know as SQL professionals that a common use of the ALTER TABLE command is that we can change a tables’ structure in a myriad number of ways. And, that’s a good thing too because chances are, you won’t always nail down the initial structure. Due to changing business or application requirements, you may even have to add additional columns that were not considered during the schema design phase. Suppose you have many tables that are structured similarly and they all need a specific column added to their already-existing design. Under certain circumstances, using the MySQL Shell in Python mode (\py), can reduce the number of manual ALTER TABLE statements you have to type. Continue reading to see examples in the MySQL Shell…

Photo by elCarito …

Introduction In this article, we are going to see how we can implement an audit logging mechanism using MySQL database triggers to store the old and new row states in JSON column types. Database tables Let’s assume we have a library application that has the following two tables: The book table stores all the books that are found in our library, and the book_audit_log table stores the CDC (Change Data Capture) events that happened to a given book record via an INSERT, UPDATE, or DELETE DML statement. The book_audit_log table is created... Read More

The post MySQL audit logging using triggers appeared first on Vlad Mihalcea.

We all know as SQL professionals that the NULL marker is a special case. Oftentimes, you have NULL‘s stored in your tables, and that’s fine. Suppose you want to present an alternative value in query results where some of the columns have NULL? This is a perfect opportunity (but not the only) to use the IFNULL() function. I find IFNULL() quite useful when exporting query results to a CSV file or other type of flat file, providing something more meaningful than the NULL word itself. However, you only have one substitute for the NULL value when using IFNULL(), so keep that in mind. Continue reading and see examples using IFNULL() in SELECT queries…

Photo by …

There may come a time you need to rename one or more columns in an existing MySQL table for a variety of reasons. Using ALTER TABLE, to rename a column is an easy enough command. But, suppose there are multiple tables in the same database/schema that have the same-named column and all of those columns need to be renamed. That could be a lot of ALTER TABLE statements to type out. Save your energy and time, avoiding all of those ALTER TABLE commands all-together. If you are lucky enough to be working with a MySQL version > 8.0 then the Shell is your salvation. With just a few lines of Python code in \py mode, all of your trouble(s) and headache(s) are no more…

Photo by Ibrahim Rifath on …