MySQL and Databases

Create a Foreign Key

FOREIGN KEY (blog_userID) REFERENCES blog_users(userId) ON DELETE CASCADE

MySQL TimeStamps

MySQL TimeStamps can have 2- 14 digits (where 2 = YY and 10 would equal YYMMDDHHSS).

Tips

• Use indexes in the database tables will increase speed of queries.
• EXPLAIN shows performance differences.

You should also create separate MySQL users for different types of activity. For example, the administrator user for the site will need SELECT, INSERT, and UPDATE permissions. They may also need DELETE, but it’s best not to allow that unless absolutely necessary. Conversely, almost everything a customer will do on an e-commerce site will only require a MySQL user with SELECT privileges. Browsing and searching the catalog are simple SELECT queries. It may not be until the user starts to complete an order—actually check out—that an INSERT is required. An UPDATE would be needed if they can change their password or other personal information. DELETE permissions would never be appropriate. In theory, you could create three distinct types of MySQL users with specific permissions:

• Public: SELECT
• Customer: SELECT, INSERT, UPDATE
• Admin: SELECT, INSERT, UPDATE, DELETE

Use Fulltext search on fields which are searched

ADD FULLTEXT(product,productDescription) ... MATCH() AGAINST ()

You can round up values in your selects

SELECT ROUND(price * 1.2) FROM inventory

Select future and past dates

SELECT DATE("2016-02-01","-7 days") or SELECT DATE("now", "-1day")

Wildcard Searches

% finds zero or more characters 
- find a single character

LIKE '_frogs%'

MySQL Users

You should also create separate MySQL users for different types of activity. For example, the administrator user for the site will need SELECT, INSERT, and UPDATE permissions. They may also need DELETE, but it’s best not to allow that unless absolutely necessary. Conversely, almost everything a customer will do on an e-commerce site will only require a MySQL user with SELECT privileges. Browsing and searching the catalog are simple SELECT queries. It may not be until the user starts to complete an order—actually check out—that an INSERT is required. An UPDATE would be needed if they can change their password or other personal information. DELETE permissions would never be appropriate. In theory, you could create three distinct types of MySQL users with specific permissions:

• Public: SELECT
• Customer: SELECT, INSERT, UPDATE
• Admin: SELECT, INSERT, UPDATE, DELETE

MySQL Find and Replace

UPDATE your_table SET your_field = REPLACE(your_field, 'articles/updates/', 'articles/news/') WHERE your_field LIKE '%articles/updates/%'

References

How to Use MySQL Foreign Keys for Quicker Database Development

Understanding JOINs in MySQL and Other Relational Databases

MySQL Comparison Tools

Common MySQL Queries

Have some fun with the SQL Murder Mystery.

Style Guides

Use lowercase names for tables, in the singular form, no abbreviations (unless common standards). The primary key should end in _id.

The foreign key should be the name of the referenced table + the name of the referenced field.

Indexes should be the table name + _ix_ + field names()

ex. person_ix_first_name_last_name

Suffixes to Use on MySQL field names:

• _id (primary key)
• _status (relates to a status)
• _total
• _num (contains a number)
• _name contains a name string
• _seq
• _date
• _tally (a count of something)
• _size
• _addr (physical or intangible)

SQL Style Guide

SQL Naming Convention

Looking for other ideas, here is another SQL Best Practices and Style Guide for MySQL.

CSV Files