小菜鸟的温暖小窝
0%

简单使用Node.js连接MySql数据库进行增删改查

发表于 更新于
本文字数： 5.1k 阅读时长 ≈ 5 分钟

##连接数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
//引入mysql（既然要使用它，当然要引入啦）
const mysql = require('mysql'); 
 
//连接mysql,option可很具情况自行配置
const option = {
  host: "主机地址",
  user: "Mysql用户名",
  password: "Mysql用户密码",
  database: "数据库名称",
  port: '端口号',
  connectTimeout: 5000, // 连接超时
};
 
const connection = mysql.createConnection(option);
 
//连接开始
connection.connect(function(err){
	if(err){
		console.log("连接失败");
	}
	else{
		console.log('连接成功');
	}
});

##增

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
var userAddSql = 'INSERT INTO userinfo(Id,UserName,UserPass) VALUES(0,?,?)';
var userAddSql_Params = ['Wilson','abcd'];


//增
 connection.query(userAddSql,userAddSql_Params,function(err,result){
 	if(err){
 		console.log('[INSERT ERROR] - ',err.message);
		return;
	}
	
 	console.log('--------------------------INSERT----------------------------');
 	console.log('INSERT  ID:',result);
 	console.log('-----------------------------------------------------------------\n\n');
 });

##改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
var userModSql = 'UPDATE userinfo SET UserName =?,UserPass = ?  WHERE Id = ?';
var userModSql_Params = ['钟馗','5678',1];

//改

connection.query(userModSql,userModSql_Params,function(err,result){
if(err){
	console.log('[UPDATE ERROR] - ',err.message);
	return;
} 
  console.log('--------------------------UPDATE----------------------------');
  console.log('UPDATE affectedRows',result);
  console.log('-----------------------------------------------------------------\n\n');
});

##查

1
2
3
4
5
6
7
8
9
10
11
12
13
var userGetSql = 'SELECT * FROM userinfo';
 
 //查
 connection.query(userGetSql,function(err,result){
	if(err){
		console.log('[SELECT ERROR] - ',err.message);
	 return;
	} 
	
	console.log('--------------------------SELECT----------------------------');
	console.log(result);        
	console.log('-----------------------------------------------------------------\n\n');  
 });

##删

1
2
3
4
5
6
7
8
9
10
11
12
13
var userDelSql = 'DELETE FROM userinfo';

//删
connection.query(userDelSql,function(err,result){
if (err) {
	console.log('[DELETE ERROR] - ',err.message);
	return;
} 

console.log('--------------------------DELETE----------------------------');
console.log('DELETE affectedRows',result);
console.log('-----------------------------------------------------------------\n\n');  
});

##结束连接

1
connection.end()

##完整代码

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

//引入mysql（既然要使用它，当然要引入啦）
const mysql = require('mysql'); 
 
//连接mysql,option可很具情况自行配置
const option = {
  host: "101.201.145.134",
  user: "TestDB",
  password: "Wa8emaaN7ShSkcyR",
  database: "TestDB",
  port: '3306',
  connectTimeout: 5000, // 连接超时
};
 
const connection = mysql.createConnection(option);
 
//连接开始
connection.connect(function(err){
	if(err){
		console.log("连接失败");
	}
	else{
		console.log('连接成功');
	}
});
 
 
//这里就是增删改查的地方啦
 
var userAddSql = 'INSERT INTO userinfo(Id,UserName,UserPass) VALUES(0,?,?)';
var userAddSql_Params = ['Wilson','abcd'];


//增
connection.query(userAddSql,userAddSql_Params,function(err,result){
	if(err){
		console.log('[INSERT ERROR] - ',err.message);
		return;
	}
	
	console.log('--------------------------INSERT----------------------------');
	console.log('INSERT  ID:',result);
	console.log('-----------------------------------------------------------------\n\n');
});
 
 
 
 
 var userModSql = 'UPDATE userinfo SET UserName =?,UserPass = ?  WHERE Id = ?';
 var userModSql_Params = ['钟馗','5678',1];
 
 //改
 
 connection.query(userModSql,userModSql_Params,function(err,result){
	if(err){
		console.log('[UPDATE ERROR] - ',err.message);
		return;
	} 
	  console.log('--------------------------UPDATE----------------------------');
	  console.log('UPDATE affectedRows',result);
	  console.log('-----------------------------------------------------------------\n\n');
 });
 
 
 var userGetSql = 'SELECT * FROM userinfo';
 
 //查
 connection.query(userGetSql,function(err,result){
	if(err){
		console.log('[SELECT ERROR] - ',err.message);
	 return;
	} 
	
	console.log('--------------------------SELECT----------------------------');
	console.log(result);        
	console.log('-----------------------------------------------------------------\n\n');  
 });
 
 
 var userDelSql = 'DELETE FROM userinfo';
 
 //删
 connection.query(userDelSql,function(err,result){
	if (err) {
		console.log('[DELETE ERROR] - ',err.message);
		return;
	} 
	
	console.log('--------------------------DELETE----------------------------');
	console.log('DELETE affectedRows',result);
	console.log('-----------------------------------------------------------------\n\n');  
 });
 
 
 //结束连接
 connection.end()


学自：https://www.cnblogs.com/zhongweiv/p/nodejs_mysql.html