educational

An Introduction to MySQL: Part 2

In Part 1 of his MySQL series, Cracker provided us with a basic understanding of how to connect to the server, select the database, and perform some basic commands. In this installment, he'll cover the concepts and techniques needed to setup up the database for manipulation.

A database is really nothing more than a hierarchy of increasingly complex data structures, and in MySQL, the acknowledged structure for holding blocks (or records) of information is called the table. These records, in turn, are made up of the smallest object that can be manipulated by the user, objects known as the datatype. Together one or more of these datatypes will form a record. A table holds the collection of records that make up part of the database. We can consider the hierarchy of a database to be that of the following: Database < Table < Record < Datatype

Datatypes come in several forms and sizes, allowing the programmer to create tables suited for the scope of the project. The decisions made in choosing proper datatypes greatly influence the database's performance, so it is wise to have a detailed understanding of these concepts.

MySQL Datatypes
MySQL is capable of many of the datatypes that even the novice programmer has probably been exposed to. Some of the more commonly used include:

• CHAR (M) CHAR's are used to represent fixed length strings. A CHAR string can range from 1-255 characters. In later table creation, an example CHAR datatype would be declared as follows, for example: car_model CHAR(10);

• VARCHAR (M) VARCHAR is a more flexible form of the CHAR data type. It also represents data of type String, yet stores this data in variable length format. Again, VARCHAR can hold 1-255 characters. VARCHAR is usually a wiser choice than CHAR, due to it's variable length format characteristic. Although, keep in mind that CHAR is much faster than VARCHAR, sometimes up to 50%. (A CHAR stores the whole length of the declared variable, regardless of the size of the data contained within, whereas a VARCHAR only stores the length of the data, thus reducing size of the database file.) For example: car_model VARCHAR(10);

• INT (M) [Unsigned] The INT datatype stores integers ranging from -2147483648 to 2147483647. Optionally, "unsigned" can be denoted with the declaration, modifying the range to be 0 to 4294967295, ex.: light_years INT; (Valid integer: '-24567'. Invalid integer: '3000000000').

- or - light_years INT unsigned; (Valid integer: '3000000000'. Invalid integer: '-24567').

• FLOAT [(M,D)] A FLOAT represents small decimal numbers, used when a somewhat more precise representation of a number is required, for example: rainfall FLOAT (4,2); Note: Due to the fact that FLOAT is rounded, those wishing to represent money values would find it wise to use DECIMAL, a datatype found within MySQL that does not round values. Consult the MySQL server's documentation for a complete explanation.

• DATE Stores date related information. The default format is 'YYYY-MM-DD', and ranges from '0000-00-00' to '9999-12-31'. MySQL provides a powerful set of date formatting and manipulation commands, too numerous to be covered within this article. However, one can find these functions covered in detail within the MySQL documentation. Thusly: the_date DATE;

• TEXT / BLOB The text and blob datatypes are used when a string of 255 - 65535 characters is required to be stored. This is useful when one would need to store an article such as the one you are reading. However, there is no end space truncation as with VARCHAR AND CHAR. The only difference between BLOB and TEXT is that TEXT is compared case insensitively, while BLOB is compared case sensitively.

• SET A datatype of type string that allows one to choose from a designated set of values, be it one value or several values. One can designate up to 64 values, for example: transport SET ("truck", "wagon") NOT NULL;

From the above declaration, the following values can be held by transport: "", "truck", "wagon", "truck,wagon"

• ENUM A datatype of type string that has the same characteristics as the SET datatype, but only one set of allowed values may be chosen. Usually only takes up one byte of space, thus saving time and space within a table: transport ENUM ("truck", "wagon") NOT NULL;

From the above declaration, the following values can be held by transport: "", "truck", "wagon"

• Records Together, a group of declared datatypes form what is known as a record. A record can be as small as one data variable, or as many as deemed needed. One or more records form the structure of a table.

The Bigger Picture: Tables
Before we can execute commands on the database, we must first create a table in which data can be stored. This is accomplished in the following manner:

mysql> CREATE TABLE test (
> name VARCHAR (15),
> email VARCHAR (25),
> phone_number INT,
> ID INT NOT NULL AUTO_INCREMENT,
> PRIMARY KEY (ID));

Ensuing output:

Query OK, 0 rows affected (0.10 sec)

mysql>

The first table in your database has now been created. Remember that no two tables can have the same name, and that each dataspace is more often referred to as a column. Before we can execute commands on the database, we must first create a table in which data can be stored.

Column Characteristics
A column's name may not be made up of strictly numbers, but may start with a number, and include up to 64 characters. The following options can be placed after any datatype, adding other characteristics and capabilities to them:

• Primary Key: Used to differentiate one record from another. No two records can have the same primary key. This is obviously useful when it is imperative that no two records are mistaken to be the other.

• Auto_Increment: A column with this function is automatically incremented one value (previous + 1) when an insertion is made into the record. The datatype is automatically incremented when 'NULL' is inserted into the column.

• NOT NULL: Signifies that the active column can never be assigned a NULL value, for example:

soc_sec_number INT PRIMARY KEY;

Because no two soc_sec_number records can hold the same value, for example, ID_NUMBER INT AUTO_INCREMENT; can be used to automatically increments in value, starting at '1', with every subsequent insertion.

Table Relevant Commands
We can execute a number of useful commands pertaining to the tables, such as the following:

• Show Tables mysql> show tables;

Result: This will list all tables currently existing within the database.

• Show Columns mysql> show columns from test;

Result: This will return the columns and column information pertaining to the designated table.

Take a minute to execute each one of the above commands after you have created the test table. They will prove very helpful as your database increases in size and complexity.

You should now have a basic understanding of the creation of tables, one of the most important concepts of using the MySQL server. You now know that tables are constructed using datatypes, which when grouped together form a record. In the next section, we will begin learning how to actually manipulate the database.

Copyright © 2025 Adnet Media. All Rights Reserved. XBIZ is a trademark of Adnet Media.
Reproduction in whole or in part in any form or medium without express written permission is prohibited.

More Articles

opinion

Breaking Down HB 805 and How it Affects the Adult Industry

North Carolina House Bill 805 was enacted July 29, after the state legislature overrode Governor Josh Stein’s veto. The provisions that relate to the adult industry, imposing requirements for age verification, consent and content removal, are scheduled to become effective Dec. 1. Platforms have until then to update their policies and systems to comply with the new regulations.

Corey D. Silverstein ·
opinion

Staying Compliant With Payment Standards Across Europe and Australia

So, you’ve got your eye on international growth. Smart move. No matter where adult-industry merchants operate, however, one requirement remains consistent: regulatory compliance. This isn’t just a legal checkbox — it’s a critical component of keeping payments flowing and business operations intact.

Jonathan Corona ·
opinion

How to Avoid Copyright Pitfalls When Using Music in Adult Content

When creating an adult video, bringing your vision to life often means assembling just the right ingredients — including the right music. However, adding music to adult content can raise complex legal and ethical issues.

Lawrence G. Walters ·
opinion

New Visa Rules Adult Merchants Need to Know

In December 2024, I shared an update on the upcoming rollout of Visa’s Acquirer Monitoring Program, also known as VAMP. The final version went into effect in June, and enforcement will begin in October. With just a month to go, now is the time to review what’s changing and how to stay compliant.

Cathy Beardsley ·
opinion

WIA Profile: Lainie Speiser

With her fiery red hair and a laugh that practically hugs you, Lainie Speiser is impossible to miss. Having repped some of adult’s biggest stars during her 30-plus years in the business, the veteran publicist is also a treasure trove of tales dating back to the days when print was king and social media not even a glimmer in the industry’s eye.

Women in Adult ·
opinion

Fighting Back Against AI-Fueled Fake Takedown Notices

The digital landscape is increasingly being shaped by artificial intelligence, and while AI offers immense potential, it’s also being weaponized. One disturbing trend that directly impacts adult businesses is AI-powered “DMCA takedown services” generating a flood of fraudulent Digital Millennium Copyright Act (DMCA) notices.

Corey D. Silverstein ·
opinion

Building Seamless Checkout Flows for High-Risk Merchants

For high-risk merchants such as adult businesses, crypto payments are no longer just a backup plan — they’re fast becoming a first choice. More and more businesses are embracing Bitcoin and other digital currencies for consumer transactions.

Jonathan Corona ·
opinion

What the New SCOTUS Ruling Means for AV Laws and Free Speech

On June 27, 2025, the United States Supreme Court handed down its landmark decision in Free Speech Coalition v. Paxton, upholding Texas’ age verification law in the face of a constitutional challenge and setting a new precedent that bolsters similar laws around the country.

Lawrence G. Walters ·
opinion

What You Need to Know Before Relocating Your Adult Business Abroad

Over the last several months, a noticeable trend has emerged: several of our U.S.-based merchants have decided to “pick up shop” and relocate to European countries. On the surface, this sounds idyllic. I imagine some of my favorite clients sipping coffee or wine at sidewalk cafés, embracing a slower pace of life.

Cathy Beardsley ·
profile

WIA Profile: Salima

When Salima first entered the adult space in her mid-20s, becoming a power player wasn’t even on her radar. She was simply looking to learn. Over the years, however, her instinct for strategy, trust in her teams and commitment to creator-first innovation led her from the trade show floor to the executive suite.

Women in Adult ·
Show More