Perl MySQL Create Table Tutorial

Summary: in this tutorial, we will show you how to use the Perl DBI API to create tables in a MySQL database.

Creating a table steps

Before doing anything else with the databases e.g., insert, update, delete, and query data, you need to create new tables to store the data.

To create a new table, you use the following steps:

Connect to the MySQL database.
Execute the CREATE TABLE statement by calling the do() method of the database handle object.
Disconnect from the MySQL database.

Creating a table example

We are going to create three tables in the perlmysqldb sample database:

links: stores URLs including link_id, title, URL, and target.
tags: stores tags for links including tag_id and tag.
link_tags: stores the relationship between the links and tags tables, containing two fields link_id and tag_id.

The following diagram illustrates the tables:

The following is the Perl script that creates the tables:

#!/usr/bin/perl
use strict;
use warnings;
use v5.10; # for say() function

use DBI;

say "Perl MySQL Create Table Demo";

# MySQL database configurations
my $dsn = "DBI:mysql:perlmysqldb";
my $username = "root";
my $password = '';

# connect to MySQL database
my %attr = (PrintError=>0, RaiseError=>1);

my $dbh = DBI->connect($dsn,$username,$password, \%attr);

# create table statements
my @ddl =     (
		# create tags table
		"CREATE TABLE tags (
			tag_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
			tag varchar(255) NOT NULL
	        ) ENGINE=InnoDB;",
	       # create links table
	       "CREATE TABLE links (
		  link_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
		  title varchar(255) NOT NULL,
		  url varchar(255) NOT NULL,
		  target varchar(45) NOT NULL
		) ENGINE=InnoDB;",
		# create link_tags table
		"CREATE TABLE link_tags (
		  link_id int(11) NOT NULL,
		  tag_id int(11) NOT NULL,
		  PRIMARY KEY (link_id,tag_id),
		  KEY fk_link_idx (link_id),
		  KEY fk_tag_idx (tag_id),
		  CONSTRAINT fk_tag FOREIGN KEY (tag_id) 
		     REFERENCES tags (tag_id),
		  CONSTRAINT fk_link FOREIGN KEY (link_id) 
			 REFERENCES links (link_id) 
		) ENGINE=InnoDB"
	       );

# execute all create table statements	       
for my $sql(@ddl){
  $dbh->do($sql);
}	       

say "All tables created successfully!";

# disconnect from the MySQL database
$dbh->disconnect();Code language: Perl (perl)

How it works.

First, create a connection to the perlmysqldb database by using the connect() method.
Next, define an array @ddl that contains three CREATE TABLE statements.
Then, call do() method of the database handles the object to execute each CREATE TABLE statement inside the loop. We often use the do() method for executing a non-select statement that does not return a result set.
After that, display a message to indicate that all tables were created successfully.
Finally, disconnect from the database.

The output of the script is as follows:

Perl MySQL Create Table Demo
All tables created successfully!

Now, you can check the perlmysqldb database to verify if the tables have been created successfully.

In this tutorial, you have learned how to create new tables from a Perl program in a MySQL database by using the do() method of the database handle object.

Was this tutorial helpful?