MySQL Table Creation and Random Data Population: A Comprehensive Guide

Creating a MySQL Table with Auto-Increment ID and Random Content

This guide demonstrates how to create a MySQL table named 't3' with an auto-incrementing ID and a content column. We will then populate the table with 100 random data entries using a SQL script.

1. Creating the Table

CREATE TABLE mydb.t3 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content VARCHAR(100) NULL
);

This SQL statement defines a table named 't3' in the database 'mydb'. It includes:

• id: An integer column that automatically increments with each new row, serving as the primary key.
• content: A varchar column that can store up to 100 characters and allows for null values.

2. Populating the Table with Random Data

INSERT INTO mydb.t3 (content) 
SELECT CONCAT('Content ', FLOOR(RAND()*1000)) 
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) AS t1 
CROSS JOIN (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) AS t2 
LIMIT 100;

This script generates 100 random content values and inserts them into the 't3' table:

• *SELECT CONCAT('Content ', FLOOR(RAND()1000)): Generates a random number between 0 and 999 and combines it with 'Content ' to create a random string.
• FROM (SELECT 1 UNION SELECT 2 ... UNION SELECT 10) AS t1: Creates a temporary table 't1' with values 1 to 10.
• CROSS JOIN (SELECT 1 UNION SELECT 2 ... UNION SELECT 10) AS t2: Creates a second temporary table 't2' with values 1 to 10 and joins it with 't1' to generate 100 combinations.
• LIMIT 100: Selects only the first 100 rows from the joined result.

Conclusion

By following these steps, you have successfully created a MySQL table named 't3' with an auto-incrementing ID and a content column. You have also populated it with 100 random data entries. This guide provides a basic foundation for managing your data effectively using SQL.