DC Web Makers Blogs & News

15 Tricks That Any Serious MySQL Database Developer Should Know (Part 1)

Without a doubt, the MySQL database is the most popular database that already exists. There are countless tricks, options, and functions avail...

Without a doubt, the MySQL database is the most popular database that already exists. There are countless tricks, options, and functions available for MySQL developers. The developers often are surrounded by many technical and detailed contents which can easily distract them from MySQL's big picture. The problem is that they lack a checklist of all the essential techniques to master. I have developed and normalized many advance MySQL databases (along with IT consulting), which motivated me to write this article. What follows are 15 essential tricks or things that any serious MySQL developer should know:

1. Table Relationships
There are three forms of table relationships: one-to-one, One-to-Many (O2M), and Many-to-Many (M2M). My analogy for O2M is it is a parent-children relationship where one parent can have many children but not the other way around. Thus, always remember putting the Foreign Key (FK) in the children (many) table. For M2M, you just need to create a lookup table.

2. Join Tables
There are many forms of MySQL table joins: INNER, OUTER, NATURAL, LEFT, AND RIGHT. Surprising enough, I use none of them. For example, consider user and user_pic tables where the user is a parent table with many children or pictures. As mentioned before, we put FK (user_id) in the user_pic table and join them together (note that user_pk is the primary key of user table):
"SELECT * FROM user, user_pic WHERE user.user_pk=user_pic.user_id"
Now the two tables are joined together. Except for special cases where you may use other joins (like LEFT join for including NULL values), this approach will handle most of your jobs.

3. InnoDB Engine
If you are fan of MySQL InnoDB engine and normalized database structures, I suggest setting "UPDATE=cascade" and "DELETE=restricted" when creating the table relationships.

4. Cross Database Queries
Big or enterprise level databases require cross-database queries. For example, database one (company_users) has a user table and database two (company_staffs) has a staff table. Here is a one cross database query:
"SELECT * FROM company_users.user, company_staffs.staff WHERE ...."

5. Lovely Aliases
I use aliases a lot mainly in four situations:
1. When joining multiple tables together to avoid confusions and shorter queries
2. When making cross-database queries
3. When there are ambiguous queries (for instance, joining two tables that have at least one field in common)
4. When using the DATE-FORMAT function to distinguish the formatted date from Timestamp one.

Stay tuned for Part 2.....

DC Web Makers Blogs
15 Tricks MySQL Developer (Part 1)

Blog & News Details

15 Tricks That Any Serious MySQL Database Developer Should Know (Part 1)

  • Posted: Tuesday February 12th
  • Without a doubt, the MySQL database is the most popular database that already exists. There are countless tricks, options, and functions available for MySQL developers. The developers often are surrounded by many technical and detailed contents which can easily distract them from MySQL's big picture. The problem is that they lack a checklist of all the essential techniques to master. I have developed and normalized many advance MySQL databases (along with IT consulting), which motivated me to write this article. What follows are 15 essential tricks or things that any serious MySQL developer should know:

    1. Table Relationships
    There are three forms of table relationships: one-to-one, One-to-Many (O2M), and Many-to-Many (M2M). My analogy for O2M is it is a parent-children relationship where one parent can have many children but not the other way around. Thus, always remember putting the Foreign Key (FK) in the children (many) table. For M2M, you just need to create a lookup table.

    2. Join Tables
    There are many forms of MySQL table joins: INNER, OUTER, NATURAL, LEFT, AND RIGHT. Surprising enough, I use none of them. For example, consider user and user_pic tables where the user is a parent table with many children or pictures. As mentioned before, we put FK (user_id) in the user_pic table and join them together (note that user_pk is the primary key of user table):
    "SELECT * FROM user, user_pic WHERE user.user_pk=user_pic.user_id"
    Now the two tables are joined together. Except for special cases where you may use other joins (like LEFT join for including NULL values), this approach will handle most of your jobs.

    3. InnoDB Engine
    If you are fan of MySQL InnoDB engine and normalized database structures, I suggest setting "UPDATE=cascade" and "DELETE=restricted" when creating the table relationships.

    4. Cross Database Queries
    Big or enterprise level databases require cross-database queries. For example, database one (company_users) has a user table and database two (company_staffs) has a staff table. Here is a one cross database query:
    "SELECT * FROM company_users.user, company_staffs.staff WHERE ...."

    5. Lovely Aliases
    I use aliases a lot mainly in four situations:
    1. When joining multiple tables together to avoid confusions and shorter queries
    2. When making cross-database queries
    3. When there are ambiguous queries (for instance, joining two tables that have at least one field in common)
    4. When using the DATE-FORMAT function to distinguish the formatted date from Timestamp one.

    Stay tuned for Part 2.....