MySQL Programming Languages

Overview

MySQL provides native connectors and drivers for most programming languages, allowing applications to interact with databases through various APIs.

Supported Languages

• PHP: Native extensions (mysqli, PDO)
• Python: mysql-connector-python, MySQLdb / Python：mysql-connector-python、MySQLdb
• Java: JDBC connector
• Node.js: mysql, mysql2 / Node.js：mysql、mysql2
• C#: MySql.Data, MySqlConnector / C#：MySql.Data、MySqlConnector
• Ruby: mysql2 gem / Ruby：mysql2 gem
• Go: go-sql-driver / Go：go-sql-driver
• Other: Perl, C++, etc.

PHP / PHP

mysqli Extension

<?php
// Basic connection
$conn = new mysqli('localhost', 'username', 'password', 'database_name');

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Execute query
$result = $conn->query("SELECT * FROM users");

// Fetch results
while ($row = $result->fetch_assoc()) {
    echo "Name: " . $row['name'] . "<br>";
}

// Prepared statement
$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $user_id);
$stmt->execute();
$result = $stmt->get_result();

// Insert with prepared statement
$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);
$stmt->execute();

// Close connection
$conn->close();
?>

PDO (PHP Data Objects)

<?php
// Connection with PDO
$conn = new PDO(
    'mysql:host=localhost;dbname=database_name',
    'username',
    'password',
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]
);

// Query with fetch
$stmt = $conn->query("SELECT * FROM users");
while ($row = $stmt->fetch()) {
    echo "Name: " . $row['name'] . "<br>";
}

// Prepared statement
$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$user_id]);
$user = $stmt->fetch();

// Transaction
try {
    $conn->beginTransaction();
    
    $conn->exec("INSERT INTO orders (user_id, total) VALUES (1, 100)");
    $conn->exec("UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 10");
    
    $conn->commit();
} catch (Exception $e) {
    $conn->rollBack();
    echo "Transaction failed: " . $e->getMessage();
}
?>

Python / Python

mysql-connector-python / mysql-connector-python

import mysql.connector
from mysql.connector import Error

# Basic connection
try:
    conn = mysql.connector.connect(
        host='localhost',
        user='username',
        password='password',
        database='database_name'
    )
    
    # Execute query
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    
    # Fetch results
    results = cursor.fetchall()
    for row in results:
        print(f"Name: {row['name']}")
    
    # Prepared statement
    cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
    user = cursor.fetchone()
    
    # Insert
    cursor.execute(
        "INSERT INTO users (name, email) VALUES (%s, %s)",
        (name, email)
    )
    conn.commit()
    
    # Transaction
    conn.start_transaction()
    try:
        cursor.execute("INSERT INTO orders (user_id, total) VALUES (%s, %s)", (1, 100))
        cursor.execute("UPDATE inventory SET quantity = quantity - 1 WHERE product_id = %s", (10,))
        conn.commit()
    except:
        conn.rollback()
        raise
    
except Error as e:
    print(f"Error: {e}")
finally:
    if conn.is_connected():
        cursor.close()
        conn.close()

Connection Pooling

from mysql.connector import pooling

# Create connection pool
pool = pooling.MySQLConnectionPool(
    pool_name="mypool",
    pool_size=5,
    host='localhost',
    user='username',
    password='password',
    database='database_name'
)

# Get connection from pool
conn = pool.get_connection()
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")

# Return connection to pool
cursor.close()
conn.close()

Java / Java

JDBC Connection

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class MySQLExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/database_name";
        String user = "username";
        String password = "password";
        
        try {
            // Load driver
            Class.forName("com.mysql.cj.jdbc.Driver");
            
            // Establish connection
            Connection conn = DriverManager.getConnection(url, user, password);
            
            // Execute query
            java.sql.Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM users");
            
            // Fetch results
            while (rs.next()) {
                String name = rs.getString("name");
                String email = rs.getString("email");
                System.out.println("Name: " + name);
            }
            
            // Prepared statement
            PreparedStatement pstmt = conn.prepareStatement(
                "SELECT * FROM users WHERE id = ?"
            );
            pstmt.setInt(1, userId);
            ResultSet rs2 = pstmt.executeQuery();
            
            // Insert
            PreparedStatement insertStmt = conn.prepareStatement(
                "INSERT INTO users (name, email) VALUES (?, ?)"
            );
            insertStmt.setString(1, name);
            insertStmt.setString(2, email);
            insertStmt.executeUpdate();
            
            // Transaction
            conn.setAutoCommit(false);
            try {
                stmt.executeUpdate("INSERT INTO orders (user_id, total) VALUES (1, 100)");
                stmt.executeUpdate("UPDATE inventory SET quantity = quantity - 1");
                conn.commit();
            } catch (SQLException e) {
                conn.rollback();
                throw e;
            }
            
            // Close resources
            rs.close();
            rs2.close();
            stmt.close();
            pstmt.close();
            insertStmt.close();
            conn.close();
            
        } catch (ClassNotFoundException e) {
            System.out.println("Driver not found");
        } catch (SQLException e) {
            System.out.println("SQL error: " + e.getMessage());
        }
    }
}

Connection Pooling with HikariCP

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class ConnectionPoolExample {
    public static void main(String[] args) {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/database_name");
        config.setUsername("username");
        config.setPassword("password");
        config.setMaximumPoolSize(10);
        config.setMinimumIdle(5);
        
        HikariDataSource ds = new HikariDataSource(config);
        
        try (Connection conn = ds.getConnection()) {
            // Use connection
            // ...
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Node.js / Node.js

mysql Package

const mysql = require('mysql');

// Basic connection
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'username',
    password: 'password',
    database: 'database_name'
});

connection.connect((err) => {
    if (err) throw err;
    console.log('Connected!');
    
    // Execute query
    connection.query('SELECT * FROM users', (error, results, fields) => {
        if (error) throw error;
        
        results.forEach(row => {
            console.log(`Name: ${row.name}`);
        });
    });
    
    // Prepared statement
    connection.query(
        'SELECT * FROM users WHERE id = ?',
        [userId],
        (error, results) => {
            if (error) throw error;
            console.log(results[0]);
        }
    );
    
    // Insert
    connection.query(
        'INSERT INTO users (name, email) VALUES (?, ?)',
        [name, email],
        (error, results) => {
            if (error) throw error;
            console.log(`Inserted ID: ${results.insertId}`);
        }
    );
    
    connection.end();
});

mysql2 Package (Promise-based)

const mysql = require('mysql2/promise');

// Basic connection
const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'username',
    password: 'password',
    database: 'database_name'
});

// Execute query
const [users] = await connection.execute('SELECT * FROM users');
users.forEach(user => {
    console.log(`Name: ${user.name}`);
});

// Prepared statement
const [user] = await connection.execute(
    'SELECT * FROM users WHERE id = ?',
    [userId]
);

// Transaction
await connection.beginTransaction();
try {
    await connection.execute('INSERT INTO orders (user_id, total) VALUES (?, ?)', [1, 100]);
    await connection.execute('UPDATE inventory SET quantity = quantity - 1', [10]);
    await connection.commit();
} catch (error) {
    await connection.rollback();
    throw error;
}

// Connection pool
const pool = mysql.createPool({
    host: 'localhost',
    user: 'username',
    password: 'password',
    database: 'database_name',
    waitForConnections: true,
    connectionLimit: 10
});

const conn = await pool.getConnection();
// Use connection...
conn.release();

C# / C#

MySql.Data / MySql.Data

using MySql.Data;
using System;

class MySQLExample {
    static void Main() {
        string connectionString = "Server=localhost;Database=database_name;Uid=username;Pwd=password;";
        
        using (MySqlConnection conn = new MySqlConnection(connectionString)) {
            try {
                conn.Open();
                Console.WriteLine("Connected!");
                
                // Execute query
                string query = "SELECT * FROM users";
                MySqlCommand cmd = new MySqlCommand(query, conn);
                
                using (MySqlDataReader reader = cmd.ExecuteReader()) {
                    while (reader.Read()) {
                        string name = reader.GetString("name");
                        Console.WriteLine($"Name: {name}");
                    }
                }
                
                // Prepared statement
                cmd.CommandText = "SELECT * FROM users WHERE id = @id";
                cmd.Parameters.AddWithValue("@id", userId);
                
                using (MySqlDataReader reader = cmd.ExecuteReader()) {
                    if (reader.Read()) {
                        Console.WriteLine(reader.GetString("name"));
                    }
                }
                
                // Insert
                cmd.CommandText = "INSERT INTO users (name, email) VALUES (@name, @email)";
                cmd.Parameters.AddWithValue("@name", name);
                cmd.Parameters.AddWithValue("@email", email);
                cmd.ExecuteNonQuery();
                
                // Transaction
                MySqlTransaction transaction = conn.BeginTransaction();
                try {
                    cmd.Transaction = transaction;
                    
                    cmd.CommandText = "INSERT INTO orders (user_id, total) VALUES (1, 100)";
                    cmd.ExecuteNonQuery();
                    
                    cmd.CommandText = "UPDATE inventory SET quantity = quantity - 1";
                    cmd.ExecuteNonQuery();
                    
                    transaction.Commit();
                } catch {
                    transaction.Rollback();
                    throw;
                }
                
            } catch (MySqlException ex) {
                Console.WriteLine($"Error: {ex.Message}");
            }
        }
    }
}

Entity Framework Core / Entity Framework Core

using Microsoft.EntityFrameworkCore;
using MySql.Data.EntityFrameworkCore;

public class User {
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

public class AppDbContext : DbContext {
    public DbSet<User> Users { get; set; }
    
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
        optionsBuilder.UseMySql(
            "Server=localhost;Database=database_name;Uid=username;Pwd=password;"
        );
    }
}

// Usage
using (var context = new AppDbContext()) {
    var users = context.Users.ToList();
    var user = context.Users.Find(userId);
    
    context.Users.Add(new User { Name = "John", Email = "john@example.com" });
    context.SaveChanges();
}

Ruby / Ruby

mysql2 Gem / mysql2 gem

require 'mysql2'

# Basic connection
client = Mysql2::Client.new(
    host: 'localhost',
    username: 'username',
    password: 'password',
    database: 'database_name'
)

# Execute query
results = client.query('SELECT * FROM users')
results.each do |row|
  puts "Name: #{row['name']}"
end

# Prepared statement
statement = client.prepare('SELECT * FROM users WHERE id = ?')
user = statement.execute(userId).first

# Insert
client.query('INSERT INTO users (name, email) VALUES (?, ?)', [name, email])

# Transaction
client.query('START TRANSACTION')
begin
client.query('INSERT INTO orders (user_id, total) VALUES (1, 100)')
client.query('UPDATE inventory SET quantity = quantity - 1')
rescue StandardError => e
  client.query('ROLLBACK')
  raise e
else
  client.query('COMMIT')
end

client.close

Go / Go

go-sql-driver / go-sql-driver

package main

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "log"
)

type User struct {
    ID    int
    Name  string
    Email string
}

func main() {
    // Open connection
    db, err := sql.Open("mysql", "username:password@tcp(127.0.0.1:3306)/database_name")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Execute query
    rows, err := db.Query("SELECT id, name, email FROM users")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    for rows.Next() {
        var user User
        err := rows.Scan(&user.ID, &user.Name, &user.Email)
        if err != nil {
            log.Fatal(err)
        }
        log.Printf("Name: %s", user.Name)
    }

    // Prepared statement
    var user User
    err = db.QueryRow("SELECT id, name, email FROM users WHERE id = ?", userId).Scan(
        &user.ID, &user.Name, &user.Email,
    )
    if err != nil {
        log.Fatal(err)
    }

    // Insert
    result, err := db.Exec("INSERT INTO users (name, email) VALUES (?, ?)", name, email)
    if err != nil {
        log.Fatal(err)
    }
    id, _ := result.LastInsertId()

    // Transaction
    tx, err := db.Begin()
    if err != nil {
        log.Fatal(err)
    }

    _, err = tx.Exec("INSERT INTO orders (user_id, total) VALUES (1, 100)")
    if err != nil {
        tx.Rollback()
        log.Fatal(err)
    }

    _, err = tx.Exec("UPDATE inventory SET quantity = quantity - 1")
    if err != nil {
        tx.Rollback()
        log.Fatal(err)
    }

    err = tx.Commit()
    if err != nil {
        log.Fatal(err)
    }
}

Best Practices

Security

1. Always use prepared statements for user input
2. Never concatenate user input into queries
3. Use connection pools for better resource management
4. Validate input types before database operations
5. Handle errors properly
6. Use appropriate database user privileges
7. Use SSL/TLS for remote connections

Performance

1. Use connection pooling
2. Batch insert operations when possible
3. Select only necessary columns
4. Use LIMIT for large result sets
5. Index columns used in WHERE clauses
6. Use transactions for related operations
7. Close connections when done
8. Use appropriate fetch methods

Error Handling

1. Always implement try-catch blocks
2. Log errors appropriately
3. Provide meaningful error messages
4. Implement retry logic for transient errors
5. Clean up resources in finally blocks
6. Validate data before database operations

Summary

MySQL programming language support includes:

• Multiple Languages: PHP, Python, Java, Node.js, C#, Ruby, Go
• Native Drivers: Official and community-supported connectors
• Prepared Statements: SQL injection prevention
• Connection Pooling: Efficient resource management
• Transactions: Data integrity support
• Error Handling: Robust exception management

Choose appropriate driver and follow best practices for secure, performant applications.

---

Previous: Quiz

Next: Reference Resources