į´éä¸ä¸éä¸åčĒč¨å° MySQL å CRUD įæšæŗįļäŊįč¨į¨，ä¸æäŊŋį¨ ORM æč
å
ļäģåčŊåŧˇå¤§č¤éįåĨäģļ，įĸįĢåĻææäŊŋį¨ framework é常éŊæé，ééäŊŋį¨æį°ĄæŊįæšæŗåŽæ。
MySQL
å
åģēįĢ襨åŽäžå CRUD äŊŋį¨。
CREATE TABLE `examples` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
`created_at` DATETIME NOT NULL,
PRIMARY KEY (`id`)
)
COLLATE='utf8mb4_unicode_ci';
PHP - PDO
PHP įžå¨ä¸åŽæ¯äŊŋį¨ PDO，å¯ĻåæšåŧåĻä¸。
<?php
$username = 'root';
$password = 123456;
try {
$name = 'Chan';
$createdAt = date('Y-m-d H:i:s');
$dbh = new \PDO('mysql:host=localhost;dbname=demo;charset=utf8', $username, $password);
$sql = "INSERT INTO `examples` (`name`, `created_at`) VALUES (:name, :created_at)";
$sth = $dbh->prepare($sql);
$sth->bindParam(':name', $name, \PDO::PARAM_STR);
$sth->bindParam(':created_at', $createdAt, \PDO::PARAM_STR);
$sth->execute();
$sql = "SELECT * FROM `examples`";
$sth = $dbh->prepare($sql);
$sth->execute();
$rows = $sth->fetchAll(\PDO::FETCH_ASSOC);
var_dump($sth->rowCount());
var_dump($rows);
$sql = "SELECT * FROM `examples`";
$sth = $dbh->prepare($sql);
$sth->execute();
$rows = $sth->fetch(\PDO::FETCH_ASSOC);
var_dump($rows);
} catch (Exception $e) {
var_dump($e-getMessage());
} finally {
$sth = null;
$dbh = null;
}
å
ļä¸ PDO::PARAM_STR įé¨äģŊæ¯čŽäŊ įĸēåŽåŗå
ĨæåįååĨ，å¯äģĨææé˛æĸ SQL Injection，常äŊŋį¨įæ：
- PDO::PARAM_STR
- PDO::PARAM_INT
- PDO::PARAM_BOOL
å
ļäģå¯äģĨäŊŋį¨įé¨äģŊå¯äģĨįéé，æ´æ°čˇåĒé¤įé¨äģŊå°ąæ¯æ INSERT įå
§åŽšæšæ UPDATE čˇ DELETE，å°ąä¸å¤å¯Ģį¯äžäē。
Python - MySQLdb
Python æåäŊŋį¨ MySQLdb äžæäŊ，æéēŧåŽčŖįļ˛čˇ¯ä¸æåžå¤æå¸，ééäšä¸į¤ēį¯äē。
import MySQLdb
import datetime
username = 'root'
password = '123456'
try:
db = MySQLdb.connect(host='localhost', db='demo', user=username, passwd=password)
cursor = db.cursor()
values = (
'Chan',
datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
)
sql = "INSERT INTO `examples` (`name`, `created_at`) VALUES (%s, %s)"
cursor.execute(sql, values)
db.commit()
sql = "SELECT * FROM `examples`"
cursor.execute(sql)
rows = cursor.fetchall()
print(cursor.rowcount)
print(rows)
sql = "SELECT * FROM `examples`"
cursor.execute(sql)
row = cursor.fetchone()
print(row)
except Exception as e:
print(str(e))
finally:
db.close()
éååĨäģļæ˛æååŗ key name，æäģĨåĻæäŊ æŗčĻå°ææŦäŊåį¨ąį芹å¯čŊčĻéæ¨ŖäŊŋį¨
sql = "SELECT * FROM `examples`"
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
pk, name, date = row
print(pk)
print(name)
print(date)
æ¤åĨäģļåŗéåæ¸įæšæŗåžå¤，å¯äģĨåč Python best practice and securest to connect to MySQL and execute queries éį¯。
Node.js - mysql2
node.js é¨äģŊæé¸į¨äē mysql2，äģčĒæŗåēæŦä¸čˇ mysql ä¸æ¨Ŗ，å¤äēäēåčŊčˇæčĒĒæčŊæææ，ææ¯æ˛å¯Ļæ¸Ŧ，äŊå°ąį¨ææ°į。
const mysql = require('mysql2');
const datetime = require('node-datetime');
try {
const connection = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '123456',
database: 'demo'
});
const name = 'Chan';
const dt = datetime.create();
const createdAt = dt.format('Y-m-d H:M:S');
let sql = 'INSERT INTO `examples` (`name`, `created_at`) VALUES (?, ?)';
connection.execute(sql, [name, createdAt], (err, rows, fields) => {
if (err) {
console.log(err);
}
});
sql = 'SELECT * FROM `examples`';
connection.execute(sql, (err, rows, fields) => {
console.log(rows);
});
connection.end();
} catch (e) {
console.log(e.message);
}
éååĨäģļæ˛æå°čŖå¤įæåŽįįåčŊ，čĻååŽįå°ąæ¯æŋåéŖåįŦŦä¸įčŗæ，ä¸éĸįį¨åŧįĸŧæ¯ sync æ¨ĄåŧåˇčĄį，æäģĨå
ļå¯Ļæ¯ææŠæčŽå°æ˛å¯Ģå
Ĩįčŗæ，é¤éäŊ äŊŋį¨ callback æ promise，į¤ēį¯ä¸ä¸ promise įäŊæŗ。
const mysql = require('mysql2/promise');
const datetime = require('node-datetime');
async function main() {
try {
const connection = await mysql.createConnection({
host: 'localhost',
user: 'root',
password: '123456',
database: 'demo'
});
const name = 'Chan';
const dt = datetime.create();
const createdAt = dt.format('Y-m-d H:M:S');
let sql = 'INSERT INTO `examples` (`name`, `created_at`) VALUES (?, ?)';
await connection.execute(sql, [name, createdAt]);
sql = 'SELECT * FROM `examples`';
const [rows] = await connection.execute(sql);
console.log(rows);
connection.end();
} catch (e) {
console.log(e.message);
}
}
main();
