Document Types

Owned by Computer Science Department
Last updated: Aug 05, 2023
2 min read

MySQL traditionally has been a relational database, which typically uses structured data, but as of MySQL 5.7 and later, it supports a JSON data type which can store semi-structured data. However, there's no built-in specific data type for unstructured data like free-form text, images, etc., but you can store such data using types like TEXT, BLOB, etc.

Let's look at each of these data types:

  1. Structured Data: This type of data is highly organized and formatted in a way so it's easily searchable in relational databases. The structure is usually defined by a schema that describes the organization and the type of data being stored. It includes data in relational databases (tables, rows, and columns), data in spreadsheets, etc. In MySQL, you can create a table with structured data like this:

    CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100), birth_date DATE, joined_date DATE, salary DECIMAL(8, 2) );

    Here, each column has a well-defined datatype and purpose.

  2. Semi-Structured Data: This is a type of structured data, but it is not organized in the tabular form of relational databases. It includes data like XML, JSON, etc. It does not conform to a data model but has some organizational properties. It's easier to analyze than unstructured data. In MySQL, you can use the JSON data type to store semi-structured data:

    CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100), info JSON );

    In this example, the info column could contain a variety of data about the employee in the form of a JSON object.

  3. Unstructured Data: This refers to information that doesn't have a pre-defined data model or isn't organized in a predefined manner. It includes data like text, images, audios, videos, etc. In MySQL, you can use data types like TEXT or BLOB to store unstructured data:

    CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100), notes TEXT, picture BLOB );

    In this example, notes could contain free-form text about the employee, and picture could contain an image file.

While MySQL provides the ability to store semi-structured and unstructured data, it is a relational database system optimized for structured data. Other types of databases, such as NoSQL databases (MongoDB, CouchDB, etc.), are specifically designed to work with semi-structured and unstructured data.

Â