MySQL

Connect to MySQL, run sync and async queries, and read results with cache_*.

Knight includes a MySQL driver. It is used exactly like SQLite: open a connection, run a query, read rows. Async queries return through a named callback.

Note

The driver is github.com/go-sql-driver/mysql. Use a standard DSN, e.g. user:pass@tcp(127.0.0.1:3306/dbname?parseTime=true.

1. Open a connection

knight

system::start() {
    let db = mysql_open("root:secret@tcp(127.0.0.1:3306/knight?parseTime=true");
    if (db == null) {
        Log("MySQL connection failed");
        return;
    }
    Log("MySQL handle: {db}");

    // Create a table (synchronous is fine for one-off setup).
    mysql_tquery(db, "CREATE TABLE IF NOT EXISTS accounts (name VARCHAR(24) PRIMARY KEY, hash VARCHAR(255), score INT DEFAULT 0)", "onReady");
};

function onReady(result, err) {
    if (err != "") { Log("setup error: " + err); }
    else { Log("database ready"); }
};

Warning

mysql_open returns a handle (an integer), or null on failure. Store it at module level so you do not reconnect for every query.

2. Run a query

mysql_tquery runs the query on a worker thread and calls the named function with (result, err) when it finishes.

knight

function onLoadScore(result, err) {
    if (err != "") {
        Log("query failed: " + err);
        return;
    }
    let rows = cache_get_row_count(result);
    if (rows == 0) {
        Log("no account found");
        return;
    }
    let score = cache_get_value_name_int(result, 0, "score");
    let name  = cache_get_value_name(result, 0, "name");
    Log("{name} has score {score}");
}

player::command("/load") {
    mysql_tquery(DB, "SELECT name, score FROM accounts WHERE name = 'CJ'", "onLoadScore");
};

3. Run a write

Use mysql_tquery for INSERT/UPDATE/DELETE too. The callback still receives (result, err); check err.

knight

function onSaved(result, err) {
    if (err != "") { Log("save failed: " + err); return; }
    Log("saved");
}

player::command("/save", int score) {
    let q = mysql_format(DB, "INSERT INTO accounts (name, score) VALUES ('%e', %d) ON DUPLICATE KEY UPDATE score = %d",
                          PlayerName(playerid), score, score);
    mysql_tquery(DB, q, "onSaved");
};

Warning

mysql_tquery and mysql_pquery do not bind extra arguments. Build the query with mysql_format and its placeholders so input is escaped.

4. mysql_format placeholders

Placeholder	Meaning
`%s`	String, escaped and quoted
`%e`	String, escaped but not quoted (for `LIKE ... '%%e%%'`)
`%d`	Integer
`%f`	Float

knight

let q = mysql_format(DB, "SELECT * FROM accounts WHERE name = '%s'", "CJ");

5. Reading results

Function	Returns
`cache_get_row_count(result)`	Number of rows
`cache_get_field_count(result)`	Number of columns
`cache_get_value_name(result, row, "col")`	String value
`cache_get_value_name_int(result, row, "col")`	Integer value
`cache_get_value_name_float(result, row, "col")`	Float value
`cache_get_value_index_int(result, row, col)`	Integer by column index
`cache_insert_id()`	Last insert id
`cache_delete(result)`	Free the result

knight

function onAll(result, err) {
    if (err != "") { return; }
    let n = cache_get_row_count(result);
    for (let i = 0; i < n; i += 1) {
        Log(cache_get_value_name(result, i, "name"));
    }
}

Tip

Iterate rows with a normal for loop driven by cache_get_row_count(result). Always call cache_delete(result) when done.

6. Errors

knight

let errno = mysql_errno(DB);
if (errno != 0) {
    Log("mysql error {errno}");
}

7. Close

knight

system::stop() {
    mysql_close(DB);
};

8. Connection pooling

Each open handle keeps a small pool (8 open connections, 4 idle). Reuse one handle instead of opening a new connection per query.

9. SQL injection

Warning

Never build SQL by concatenating user input. Always use mysql_format (%s/%e/%d/%f) or ? placeholders with the synchronous query.