操作MySQL(连接/创建表/插入/选择/拖放/事务)

Contents [hide]

输出结果

Result can be matched with following regex expression.

1
r"1\tname1\tmale\t\d+/\d+/\d+ \d+:\d+:\d+\t\d+/\d+/\d+ \d+:\d+:\d+\n2\tname2\tfemale\t\d+/\d+/\d+ \d+:\d+:\d+\t\d+/\d+/\d+ \d+:\d+:\d+$"

结果示例

1
2
1   name1   male    2019/10/29 18:04:45 2019/10/29 18:04:45
2   name2   female  2019/10/29 18:04:45 2019/10/29 18:04:45

先决条件

Prepare .env file storing db connection information like this.

1
2
3
DBHOST=0.0.0.0
DBUSER=exampleuser
DBPASSWORD=example20141024

执行以下sql并为数据库和用户准备脚本

1
2
3
4
5
6
7
8
9
CREATE USER 'exampleuser'@'localhost' IDENTIFIED BY 'example20141024';
CREATE USER 'exampleuser'@'10.%' IDENTIFIED BY 'example20141024';
CREATE USER 'exampleuser'@'172.%' IDENTIFIED BY 'example20141024';
CREATE USER 'exampleuser'@'192.%' IDENTIFIED BY 'example20141024';
CREATE DATABASE example CHARACTER SET utf8mb4;
GRANT ALL PRIVILEGES ON example.* TO 'exampleuser'@'localhost';
GRANT ALL PRIVILEGES ON example.* TO 'exampleuser'@'10.%';
GRANT ALL PRIVILEGES ON example.* TO 'exampleuser'@'172.%';
GRANT ALL PRIVILEGES ON example.* TO 'exampleuser'@'192.%';

程式码


JavaScript

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
class MysqlManipulator {
    constructor(opt={}) {
    this.mysql = require('mysql');
    require('dotenv').config();
    this.rdbh = this.mysql.createConnection({
      host: process.env.DBHOST,
      user: process.env.DBUSER,
      password: process.env.DBPASSWORD,
      database: 'example'
    });
  }
 
  async beginTransaction() {
    const sql = "BEGIN"
    await this.executeSQL(sql)
  }
 
  async createExampleData() {
    const datas = [
      ['name1', 'male'],
      ['name2', 'female']
    ]
    const sql = 'INSERT INTO example (name, sex) VALUES (?, ?)'
    for (const data of datas) {
      const res = await this.rdbh.query(sql, data)
    }
  }
 
  async createExampleTable() {
    const sql = `CREATE TABLE example (
      id int NOT NULL AUTO_INCREMENT,
      name VARCHAR(255) NOT NULL,
      sex ENUM('male', 'female') DEFAULT NULL,
      created_at timestamp NOT NULL DEFAULT NOW(),
      updated_at timestamp NOT NULL DEFAULT NOW(),
      PRIMARY KEY (id),
      KEY created_at_idx (created_at),
      KEY name_idx (name),
      KEY updated_at_idx (updated_at)
    );`;
    await this.executeSQL(sql);
  }
 
    async dbclose() {
    if (typeof(this.rdbh) !== "undefined") {
      await this.rdbh.end();
    }
  }
 
    async dbconnect() {
    await this.rdbh.connect();
    const timezone = "Asia/Tokyo";
    const sql = "SET TIME_ZONE = '"+ timezone +"'"
    await this.executeSQL(sql)
  }
 
  async dropExampleTable() {
    const sql = "DROP TABLE IF EXISTS example";
    await this.executeSQL(sql);
  }
 
  async endTransaction() {
    await this.rdbh.commit()
  }
 
  async executeSQL(sql) {
    try {
      const res = await this.rdbh.query(sql);
    }
    catch(err){
      const sql = "ROLLBACK"
      await this.executeSQL(sql)
      console.log(err.name + ': ' + err.message);
    }
  }
 
  async selectFromExampleTable() {
    const sql = "SELECT id, name, sex, DATE_FORMAT(created_at, '%Y/%m/%d %H:%i:%S') AS created_at, DATE_FORMAT(updated_at, '%Y/%m/%d %H:%i:%S') AS updated_at FROM example";
    try {
      await this.rdbh.query(sql, function (err, rows, fields) {
        if (err) {
          console.log('err: ' + err);
        }
        for(const row of rows){
          console.log([row.id, row.name, row.sex, row.created_at, row.updated_at].join("\t"))
        }
      });
    }
    catch(err) {
      const sql = "ROLLBACK"
      await this.executeSQL(sql)
      console.log(err.name + ': ' + err.message)
      process.exit(-1)
    }
  }
};
module.exports = MysqlManipulator;
 
if(!module.parent) {
  (async () => {
    const rdb = new MysqlManipulator()
    await rdb.dbconnect()
    await rdb.beginTransaction()
    await rdb.dropExampleTable()
    await rdb.createExampleTable()
    await rdb.createExampleData()
    await rdb.selectFromExampleTable()
    await rdb.dropExampleTable()
    await rdb.endTransaction()
    await rdb.dbclose()
  })()
}

Perl

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
package MysqlManipulator;
use strict;
use DBI;
use Dotenv;
 
sub new() {
  my $self = {};
  Dotenv->load();
  return bless $self;
}
 
sub beginTransaction(){
  my $self = shift;
  my $sql = "BEGIN";
  $self->executeSQL($sql);
}
 
sub createExampleData(){
  my $self = shift;
  my @datas = (
    ['name1', 'male'],
    ['name2', 'female']
  );
  my $sth = $self->{'rdbh'}->prepare("INSERT INTO example (name, sex) VALUES (?, ?)");
  foreach my $data_ref (@datas) {
    $sth->execute($data_ref->[0], $data_ref->[1]);
  }
  $sth->finish();
}
 
sub createExampleTable() {
  my $self = shift;
  my $sql = <<EOF;
CREATE TABLE example (
  id int NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  sex ENUM('male', 'female') DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT NOW(),
  updated_at timestamp NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id),
  KEY `created_at_idx` (`created_at`),
  KEY `name_idx` (`name`),
  KEY `updated_at_idx` (`updated_at`)
);
EOF
  $self->executeSQL($sql);
}
 
sub dbclose(){
  my $self = shift;
  $self->{'rdbh'}->disconnect();
}
 
sub dbconnect(){
  my $self = shift;
  my $database = "example";
  my $timezone = "Asia/Tokyo";
  my $dbi = 'mysql';
  $self->{'rdbh'} = DBI->connect("DBI:".$dbi.":database=".$database.";host=".$ENV{'DBHOST'}, $ENV{'DBUSER'}, $ENV{'DBPASSWORD'}, {
   AutoCommit => 0,
   PrintError => 1,
   PrintWarn => 0
   }) or die "Failed to connect to db.";
  my $sql = "SET TIME_ZONE='".$timezone."'";
  $self->{'rdbh'}->do($sql);
}
 
sub dropExampleTable(){
  my $self = shift;
  my $sql = "DROP TABLE IF EXISTS example";
  $self->executeSQL($sql);
}
 
sub endTransaction(){
  my $self = shift;
  $self->{'rdbh'}->commit();
}
 
sub executeSQL(){
  my $self = shift;
  my $sql = shift;
  $self->{'rdbh'}->do($sql) or die("Failed to execute ".$sql);
}
 
sub selectFromExampleTable(){
  my $self = shift;
  my $sql = "SELECT id, name, sex, DATE_FORMAT(created_at, '%Y/%m/%d %H:%i:%S') AS created_at, DATE_FORMAT(updated_at, '%Y/%m/%d %H:%i:%S') AS updated_at FROM example";
  my $sth = $self->{'rdbh'}->prepare($sql) or die("Failed to prepare ".$sql);
  $sth->execute() or die("Failed to execute ".$sql);
  while(my $row_ref = $sth->fetchrow_hashref) {
    print(join("\t", $row_ref->{'id'}, $row_ref->{'name'}, $row_ref->{'sex'}, $row_ref->{'created_at'}, $row_ref->{'updated_at'})."\n");
  }
  $sth->finish();
}
 
if ($0 eq __FILE__) {
  my $rdb = new MysqlManipulator();
  $rdb->dbconnect();
  $rdb->beginTransaction();
  $rdb->dropExampleTable();
  $rdb->createExampleTable();
  $rdb->createExampleData();
  $rdb->selectFromExampleTable();
  $rdb->dropExampleTable();
  $rdb->endTransaction();
  $rdb->dbclose();
}
else{
  1;
}

PHP

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
<?php
require_once __DIR__ . '/vendor/autoload.php';
 
class MysqlManipulator {
  private $rdbh;
  public function __construct($opt=[]) {
    $dotenv = Dotenv\Dotenv::create(__DIR__);
    $dotenv->load();
  }
 
  function beginTransaction() {
    $this->rdbh->beginTransaction();
  }
 
  function createExampleData(){
    $datas = [
      ['name1', 'male'],
      ['name2', 'female']
    ];
    $sql = "INSERT INTO example (name, sex) VALUES (?, ?)";
    $sth = $this->rdbh->prepare($sql);
    foreach($datas as $data) {
      $sth->execute([$data[0], $data[1]]);
    }
  }
 
  function createExampleTable(){
    $sql =  <<< EOF
CREATE TABLE example (
  id int NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  sex ENUM('male', 'female') DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT NOW(),
  updated_at timestamp NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id),
  KEY `created_at_idx` (`created_at`),
  KEY `name_idx` (`name`),
  KEY `updated_at_idx` (`updated_at`)
);
EOF;
    $this->executeSQL($sql);
  }
 
  function dbclose() {
    $this->rdbh = null;
  }
   
  function dbconnect(){
    $dbname = "example";
    $timezone = "Asia/Tokyo";
    try {
      $this->rdbh = new PDO('mysql:dbname='.$dbname.';host='.getenv('DBHOST'), getenv('DBUSER'), getenv('DBPASSWORD'));
      $this->rdbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    catch (PDOException $e) {
      die("Failed to connect: ".$e->getMessage());
    }
    $sql = "SET TIME_ZONE = '".$timezone."'";
    $this->executeSQL($sql);
  }
 
  function dropExampleTable() {
    $sql = "DROP TABLE IF EXISTS example";
    $this->executeSQL($sql);
  }
 
  function endTransaction(){
    $this->rdbh->commit();
  }
 
  function executeSQL($sql) {
    $sqls = explode(';', $sql);
    foreach ($sqls as $sql) {
      if($sql) {
        $sth = $this->rdbh->prepare($sql);
        $sth->execute();
      }
    }
  }
   
  function rollback(){
    $this->rdbh->rollback();
  }
   
  function selectFromExampleTable(){
    $sql = "SELECT id, name, sex, DATE_FORMAT(created_at, '%Y/%m/%d %H:%i:%S') AS created_at, DATE_FORMAT(updated_at, '%Y/%m/%d %H:%i:%S') AS updated_at FROM example";
    $sth = $this->rdbh->prepare($sql);
    $sth->execute();
    while($row = $sth->fetch(PDO::FETCH_ASSOC)){
      print(implode("\t", [$row['id'], $row['name'], $row['sex'], $row['created_at'], $row['updated_at']])."\n");
    }
  }
}
 
if ( !isset(debug_backtrace()[0]) ) {
  $rdb = new MysqlManipulator();
  try{
    $rdb->dbconnect();
  } catch(PDOException $e){
    echo $e->getMessage();
    exit(1);
  }
 
  try{
    $rdb->beginTransaction();
  } catch(PDOException $e){
    echo $e->getMessage();
    $rdb->dbclose();
    exit(1);
  }
 
  try {
    $rdb->dropExampleTable();
    $rdb->createExampleTable();
    $rdb->createExampleData();
    $rdb->selectFromExampleTable();
    $rdb->dropExampleTable();
    $rdb->endTransaction();
    $rdb->dbclose();
  } catch(PDOException $e){
    $rdb->rollback();
    $rdb->dbclose();
    throw $e;
  }
}

Python

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
from firstclass_dotenv import Dotenv
import mysql.connector
import os
 
class MysqlManipulator():
  def __init__(self, opt=[]):
    dotenv = Dotenv()
    dotenv.load()
 
  def beginTransaction(self):
    sql = "BEGIN"
    self.executeSQL(sql)
 
  def createExampleTable(self):
    sql = '''\
CREATE TABLE example (
  id int NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  sex ENUM('male', 'female') DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT NOW(),
  updated_at timestamp NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id),
  KEY `created_at_idx` (`created_at`),
  KEY `name_idx` (`name`),
  KEY `updated_at_idx` (`updated_at`)
);
'''
    self.executeSQL(sql)
 
  def createExampleData(self):
    datas = [
      ['name1', 'male'],
      ['name2', 'female']
    ]
    for data in datas:
      self.cursor.execute("INSERT INTO example (name, sex) VALUES (%s, %s)", [data[0], data[1]])
 
  def dbclose(self):
    self.rdbh.close()
 
  def dbconnect(self):
    dbname = "example"
    timezone = "Asia/Tokyo"
    self.rdbh = mysql.connector.connect(host=os.environ['DBHOST'], user=os.environ['DBUSER'], passwd=os.environ['DBPASSWORD'], database=dbname)
    self.cursor = self.rdbh.cursor()
    self.executeSQL("SET TIME_ZONE = '"+ timezone + "'")
 
  def dropExampleTable(self):
    sql = "DROP TABLE IF EXISTS example"
    self.executeSQL(sql)
 
  def endTransaction(self):
    self.rdbh.commit()
 
  def executeSQL(self, sql):
    self.cursor.execute(sql)
 
  def rollback(self):
    self.rdbh.rollback()
 
  def selectFromExampleTable(self):
    sql = "SELECT id, name, sex, DATE_FORMAT(created_at, '%Y/%m/%d %H:%i:%S') AS created_at, DATE_FORMAT(updated_at, '%Y/%m/%d %H:%i:%S') AS updated_at FROM example"
    self.cursor.execute(sql)
    for [id, name, sex, created_at, updated_at] in self.cursor:
      print("\t".join([str(id), name, sex, str(created_at), str(updated_at)]))
 
if __name__ == "__main__":
  rdb = MysqlManipulator()
  rdb.dbconnect()
  try:
    rdb.beginTransaction()
    try:
      rdb.dropExampleTable()
      rdb.createExampleTable()
      rdb.createExampleData()
      rdb.selectFromExampleTable()
      rdb.dropExampleTable()
      rdb.endTransaction()
    except Exception as e:
      rdb.rollback()
      print(e)
    finally:
      rdb.dbclose()
  except Exception as e:
    print(e)
  finally:
    rdb.dbclose()

Ruby

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
require 'dotenv'
require 'mysql2'
 
class MysqlManipulator
  def initialize(opt=[])
    Dotenv.load(
      File.join(File.dirname(File.expand_path(__FILE__)), '.env')
    )
  end
 
  def dbconnect
    dbname = "example"
    timezone = "Asia/Tokyo"
    @rdbh = Mysql2::Client.new(:host => ENV["DBHOST"], :username => ENV["DBUSER"], :password => ENV["DBPASSWORD"], :database => dbname)
    sql = "SET TIME_ZONE = '"+ timezone + "'"
    @rdbh.query(sql)
  end
 
  def beginTransaction
    sql = "BEGIN"
    executeSQL(sql)
  end
 
  def createExampleTable
    sql = '''
CREATE TABLE example (
  id int NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  sex ENUM("male", "female") DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT NOW(),
  updated_at timestamp NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id),
  KEY created_at_idx (created_at),
  KEY name_idx (name),
  KEY updated_at_idx (updated_at)
);
'''
    executeSQL(sql)
  end
 
  def createExampleData
    datas = [
      ['name1', 'male'],
      ['name2', 'female']
    ]
    sth = @rdbh.prepare("INSERT INTO example (name, sex) VALUES (?, ?)")
    datas.each do |data|
      sth.execute(data[0], data[1])
    end
  end
 
  def dbclose
    @rdbh.close()
  end
 
  def dropExampleTable
    sql = "DROP TABLE IF EXISTS example"
    executeSQL(sql)
  end
 
  def endTransaction
    sql = "COMMIT"
    @rdbh.query(sql)
  end
 
  def executeSQL(sql)
    @rdbh.query(sql)
  end
 
  def selectFromExampleTable
    sql = "SELECT id, name, sex, DATE_FORMAT(created_at, '%Y/%m/%d %H:%i:%S') AS created_at, DATE_FORMAT(updated_at, '%Y/%m/%d %H:%i:%S') AS updated_at FROM example"
    results = @rdbh.query(sql)
    results.each do |row|
      print([row["id"], row['name'], row['sex'], row['created_at'], row['updated_at']].join("\t") + "\n")
    end
  end
end
 
if $0 == __FILE__
  rdb = MysqlManipulator.new()
  begin
    rdb.dbconnect()
  rescue => err
    print(err)
    exit(1)
  end
 
  begin
    rdb.beginTransaction()
    begin
      rdb.dropExampleTable()
      rdb.createExampleTable()
      rdb.createExampleData()
      rdb.selectFromExampleTable()
      rdb.dropExampleTable()
      rdb.endTransaction()
    rescue => err
      puts("Automatically roll backed.")
      print(err)
      rdb.dbclose()
      exit(1)
    end
  rescue => err
    rdb.dbclose()
    print(err)
    exit(1)
  end
end