A while back I posted an article on creating MySQL databases from the command line (see here). I am a command line junkie, and usually prefer it to a GUI. I thought I would add to that last database post by sharing how I work with MySQL, designing databases and their structure in the initial phases of application development. Keep in mind, what I am going to go over in this post is strictly for quick and easy database structure design, possibly with test data, from the command line. This is not for mangling your database after you have real data in it, or else you will mangle your data!
When I am starting work on a new database, I usually have a rough idea of the table structure. Therefore, I will create a SQL (text) file with the necessary commands to create the tables and sometimes even populate test data. Once that file is created, I can simply import it into the database. As I am working on the application, if/when I need to change something in the database, I simply modify the SQL file and re-import into the database. I know, you can use things like phpMyAdmin and the GUI tools from MySQL.com, but this (for me anyway) is quicker and easier and I end up with that SQL file that is a superb road map of my initial database. If my database gets destroyed for some reason (this has happened in shared environments!), it is trivial to re-create it. It’s also a great item to add to your documentation.
Let’s start with an example, shall we? Assuming we already have our database created called mydata, let us create a very basic user table to store user names, passwords and related information. I would begin by creating a SQL file called mydata.sql that looks like this:
-–
-- Table structure for table `myusers`
-–
DROP TABLE IF EXISTS `myusers`;
CREATE TABLE `myusers` (
`id` bigint(21) NOT NULL auto_increment,
`username` varchar(36) default NULL,
`password` varchar(36) default NULL,
`realname` varchar(96) default NULL,
`email` varchar(96) default NULL,
PRIMARY KEY (`id`)
);
If you are familiar with databases at all, the contents of this file should be self explanatory. We can get into debate over field names and lengths at another time, the thing here is that this bit of text will build a table for you called myusers with the structure that you specify. Also, by adding the “DROP TABLE IF EXISTS”, you can simply modify the file and re-import to make changes. This is where the part about mangling real data comes in, because whatever is in that table will get wiped out, keep that in mind.
To import this into your database, and of course re-import later, it’s real simple. From the directory where the SQL file is located, simply issue this command:
mysql mydata < mydata.sql
This will run those SQL commands in the file just like you were typing them into the SQL console. If you edit the SQL file, you can apply those changes by running the same command. Don’t forget that you can put SQL commands in there to insert test data at the same time you create the table. Just use good old SQL statements like anywhere else.
Once you have run the above command, you should have a table that looks like what you want:
mysql> show tables;
+------------------+
| Tables_in_mydata |
+------------------+
| myusers |
+------------------+
1 row in set (0.00 sec)
mysql> desc myusers;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | bigint(21) | | PRI | NULL | auto_increment |
| username | varchar(36) | YES | | NULL | |
| password | varchar(36) | YES | | NULL | |
| realname | varchar(96) | YES | | NULL | |
| email | varchar(96) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
It’s just that easy folks, get in there and get hacking on those databases!