Article Preview

COLUMN

Deep Dive on Creating MySQL Tables

Taking a close look at the CREATE TABLE Statement

Issue: 11.1 (January/February 2013)
Author: Craig Boyd
Author Bio: Craig Boyd is currently a Sr. Data Modeler for a well-known national retailer. But in his 17 years of IT experience he has been everything from a PC Technician to iSeries System Administrator to iSeries Programmer and Sr. Technical Lead. He lives in the great state of Texas with his wife and two kids.
Article Description: No description available.
Article Length (in bytes): 5,611
Starting Page Number: 87
Article Number: 11113
Related Link(s): None

Excerpt of article text...

Introduction

When it comes to databases, a number of us care about only what we absolutely have to: tables, primary keys, schemas, and indexes. After that, our concern about other aspects of the database tend to drop off rather quickly. Part of the reason is because we are developers and not DBAs. That is not a criticism, but just a simple statement of fact. A number of us are consultants with customers who depend on us for help in keeping the systems we have installed up and running. For some, installing the database involves taking all the defaults, running the install scripts we have come up with for our application and then setting up the application. For small installations that is certainly not a bad approach and in fact may even be the recommended approach. But sometimes we have clients who have employee numbers in the hundreds or perhaps even more. For that reason, having a better understanding of how databases work can be immensely helpful.

Since nearly half of the developers surveyed by Real Software a while back use MySQL, that is the DBMS I am planning to focus on. Over the next few, maybe several, columns I want to do a deep dive into several aspects of the CREATE TABLE statement, what they mean and how they can be used.

There are basically two kinds of tables that you can create: permanent and temporary. A permanent table is exactly what you would expect. The structure and the data persist beyond the shutdown of the database server. A temporary table is only good for the life of the connection. Temp tables are very useful for stored procedures where complex or large data sets are being used. They can also be good for work tables where a program may need to temporarily store data for processing.

To create a temporary table you simply add the key word TEMPORARY to the create statement. For example: "CREATE TEMPORARY TABLE WRK_SORT_1 (<column definition...);" To create a permanent table you just omit the TEMPORARY key word.

...End of Excerpt. Please purchase the magazine to read the full article.