MySQL stores data in a tabular format, which makes it easy to visualise the information and print it out in a way that you or I can easily understand. Like all tables, it uses horizontal rows to store individual records, and vertical columns to assign labels to certain attributes about each record. For example, a table of DVD releases might have a “release date”, “film title”, “director” and so on.
In this tutorial we won’t be doing anything too technical. Instead, we’ll be focusing on learning about how MySQL represents the data you give it to store, and the limitations and potential benefits each storage type brings.
To start off, let’s create a sample set of data for us to work with in our example, to give you a good idea of the various concepts I’ll introduce shortly:
| Name | Address | Delivery Date | Parcel Received? | Delivery Comments | Previous orders |
| Alan Smith | 12 Arlington Road | 12th Aug 2008 | Yes | Customer was very happy with service | 5 |
| Kevin Wilson | 55 Kentucky Drive | 16th Aug 2008 | No | Customer wants detailed receipt | 0 |
| Jim Jeffries | 165 Main Street | 17th Aug 2008 | No | No comments | 1 |
Here you can see a table containing a whole load of information about a fictitious delivery firm. We’re storing the customer name, their address, the date their delivery is/was due, some comments about their delivery and a number which states the number of previous orders this customer has placed.
Each of these columns can be assigned a specific type based on the format of the data we’ll be storing in them. Let’s take a look at each one in turn and see the datatypes we could assign to each column
- Name - Our customer names won’t be too long, and we won’t be storing anything other than text here, so we can define the “Name” column to be of type VARCHAR. Now, VARCHAR has a useful extra property where we can define the length of the data to be stored within the column. In this case, we could define it as VARCHAR(64) to specify that our customer names will be no more than 64 characters in length. VARCHAR fields are limited to only 255 characters in length though, so for lengthier entries, we may need to use something different…
- Address - Our customer addresses could potentially be quite long, and perhaps exceed the 255 character limit imposed by VARCHAR. For our address field, we could use the TEXT datatype. TEXT can hold up to 65,536 characters (64KB) of data, which is plenty for our requirements here, and will allow us to have adequate length addresses, if necessary. Note that we can also have MEDIUMTEXT and LONGTEXT datatypes which can hold 16,777,216 (16MB) and roughly 4 billion (4GB!) characters respectively. You’ll most likely have no need for these unless you’re looking to have much longer columns than our address field. We may have use of the MEDIUMTEXT column for our comments field though…
- Delivery Date - Here, we’re going to make use of MySQL’s DATE type to store our date. MySQL stores dates in a format suitable for calcuations and operations so that if required, we can do all sorts of useful things with this information. If we wish to retrieve the DATE information, MySQL will provide it to us in the format YYYY-MM-DD (where YYYY = year, MM = month and DD = day). If we also need to know the specific date and time for a record, we can make use of DATETIME which allows us to retrieve data in the format YYYY-MM-DD HH:MM:SS (HH = hours, MM = minutes, SS = seconds). In a later tutorial, we’ll look at how we can use MySQL’s date and time functions to manipulate records of this type
- Parcel Received? - Here we’re going to make use of a simple data type called BOOLEAN which means the value can either be TRUE (1) or FALSE (0). Note that here we can also use a datatype TINYINT(1) which is simply saying that this value can be either a 1 or a 0
- Delivery Comments - In this field, we might want to make use of the MEDIUMTEXT type, just incase we wish to store a large amount of data relating to a customer’s order. If you know that you won’t be storing much data in a column, it’s wise to use just the TEXT type, or even just VARCHAR so that you can identify which fields will hold the most data.Note that the actual choice of datatype has no real impact on space requirements or efficiency, as MySQL simply uses the amount of storage space necessary to fit in whatever data you place in TEXT, MEDIUMTEXT or LONGTEXT
- Previous Orders - Here, we are just storing a numerical value, and for this we could assign a simple INT datatype, representing “integer”. Note that you might see the definition INT(11) or similar. The number in the brackets has no real bearing on the performance of the table and it doesn’t limit the size of number you can store in the INT field, although be aware that INT types have a minimum possible value of -2147483648 to 2147483647 (and if you choose to have the field unsigned i.e. with no negative values that changes to 0 to 4294967295)For our “previous orders” column this is quite the overkill though, so a simple TINYINT (-128 to 127 or 0 to 255 unsigned) or SMALLINT (-32768 to 32767 or 0 to 65535 unsigned) will do. It’s hard to forsee any customer having more than 255 previous orders, but sometimes it’s good to err on the side of caution, just incase - If this event ever did occur, the database table would become inaccurate
We can also have MEDIUMINT which gives us potential numbers into the billions and BIGINT for when billions just won’t do, and we need to move into the realm of quintillions (going all the way up to a mind boggling 18,446,744,073,709,551,615) MEDIUMINT is useful for defining unique IDs for tables (MySQL can handle tables with billions of rows each)
Some common questions when working with MySQL relate to finding out how much data MySQL might be able to handle. You may be concerned that your website or application will simply use too much data for use with MySQL or that the number of users on your site might cause performance to drop significantly. After all, every call to MySQL uses up some processor time, and the more people using it at once, the more you have to consider the speed of the operation.
Well, have no worries, because these questions have been asked many times before, with more than satisfying answers. As mentioned above, MySQL can easily handle tables with billions of rows and you can have tens of thousands of tables per database. Each of these tables can contain thousands of columns each. The first main hurdle most people encounter when using MySQL databases is the amount of disk space the table takes up, especially when using a remote web host, and that’s relatively easy to increase.
(It’s very unlikely you’ll ever reach the true limits of what MySQL can achieve, not until you’re put in charge of a web-forum with visitor numbers in the millions and post-counts in the billions!)
Now that we’ve covered the main datatypes, I’m going to show you what this table looks like in MySQL code format. You don’t need to understand what all this does at the moment, but take a look at the syntax to see how the datatypes I’ve outlined above fit into the general scheme of things when working inside MySQL.
DROP TABLE IF EXISTS `addresses`; CREATE TABLE IF NOT EXISTS `addresses` ( `name` varchar(64) NOT NULL, `address` text NOT NULL, `delivery_date` date NOT NULL, `parcel_received` tinyint(1) NOT NULL, `delivery_comments` mediumtext NOT NULL, `previous_orders` smallint(6) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `addresses` (`name`, `address`, `delivery_date`, `parcel_received` , `delivery_comments`, `previous_orders`) VALUES ('Alan Smith', '12 Arlington Road', '2008-08-12', 1, 'Customer was very happy with service', 5), ('Kevin Wilson', '55 Kentucky Drive', '2008-08-16', 0, 'Customer wants detailed receipt', 0), ('Jim Jeffries', '165 Main Street', '2008-08-17', 0, 'No comments', 1);
The code above will create a table within a MySQL database and then insert all of the values in the table above, named “addresses” here. Later on, I’ll explain all the processes going on in more detail, but for now just familiarise yourself with the idea of tables, rows and columns in MySQL and try to visualise in your mind how all this information can be represented both as data and as text displayed on a screen.
In the next tutorial we’re going to move on to see how we can perform queries on similar sets of data to ask questions of the database, and get back some useful answers.
