How to Insert Data into MySQL

Learn how to insert data into MySQL tables using the INSERT statement with practical examples and best practices.

1. Basic INSERT Statement

To insert data into a MySQL table, use the INSERT INTO statement. Here’s the general syntax:

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

Example:

INSERT INTO users (name, email, age)
VALUES ('John Doe', 'john@example.com', 30);

2. Insert Multiple Rows

You can insert multiple rows in a single query to improve performance:

INSERT INTO users (name, email, age)
VALUES 
('Alice', 'alice@example.com', 25),
('Bob', 'bob@example.com', 28),
('Charlie', 'charlie@example.com', 35);

3. Insert Without Column Names

If you provide values for all columns in the correct order, you can omit the column names:

INSERT INTO users
VALUES (4, 'David', 'david@example.com', 40);

4. Best Practices

• Always specify column names.
• Use parameterized queries to prevent SQL injection (especially in web applications).
• Validate and sanitize input data before inserting.
• Consider using INSERT IGNORE or REPLACE if you want to handle duplicate keys gracefully.

5. Example with Python (MySQL-Python)

Here’s a Python example using the MySQL-python package:

import MySQLdb

db = MySQLdb.connect("localhost", "username", "password", "database")
cursor = db.cursor()

sql = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
val = ("Eve", "eve@example.com", 29)

try:
    cursor.execute(sql, val)
    db.commit()
except:
    db.rollback()

db.close()

Conclusion

Inserting data into MySQL is straightforward, but attention to detail and following best practices can help ensure data integrity and security. Start with simple inserts and gradually explore advanced techniques like bulk inserts, ON DUPLICATE KEY handling, and prepared statements.