Nodejs MySQL database Add, delete, change, check operation

Nodejs MySQL Add, delete, change, check operation Nodejs connection MySQL Add, delete, change, check operation (reprinted from: http://blog.sina.com.cn/s/blog_5a6efa330102vctw.html)

First, prepare

Nodejs's tutorials are mostly about manipulating MongoDB as an example. But MongoDB has some limitations, specific official online has said. I'm going to use MySQL, because how much still a bit of experience. First, research is the main. Node-mysql, is currently the most fire node under the MySQL driver. The initial use of a bit, because the asynchronous callback this way, sure enough pits.

The package name for the project below is MySQL and the version is [email protected] 2.5.4

The code shown below is preceded by the following code, which is not followed by the description

var connection = Mysql.createconnection ({

host : ' 127.0.0.1 ',

user : ' root ',

password: ' root123 ',

Port: ' 3306 ',

database: ' My_news_test ',

});

Code what the meaning is straightforward, if you want to go deep, you can visit the above official website to check. Configurations such as Host,user, which have been written to the MySQL database application, should be clear and modify the parameters themselves. The following code assumes that there is a table called Node_use in the database "My_news_test" with 3 properties in the table

ID: self-increment primary key

Name: a unique restriction

Age

Test MySQL MySQL version: 5.5

Second, build the library and insert 5 records

Source Database : My_news_test

SET foreign_key_checks=0;

-- ----------------------------

--Table structure for Node_user

-- ----------------------------

DROP TABLE IF EXISTS ' Node_user ';

CREATE TABLE ' Node_user ' (

' id ' int (one) not NULL auto_increment,

' name ' varchar (() DEFAULT NULL,

' age ' int (8) DEFAULT NULL,

PRIMARY KEY (' id ')

) Engine=innodb auto_increment=6 DEFAULT Charset=utf8;

-- ----------------------------

--Records of Node_user

-- ----------------------------

INSERT into ' node_user ' VALUES (' 1 ', ' admin ', ' 32 ');

INSERT into ' node_user ' VALUES (' 2 ', ' dans88 ', ' 45 ');

INSERT into ' node_user ' VALUES (' 3 ', ' Zhang San ', ' 35 ');

INSERT into ' node_user ' VALUES (' 4 ', ' ABCDEF ', ' 88 ');

INSERT into ' node_user ' VALUES (' 5 ', ' Li Xiao ', ' 65 ');

Third, test the environment first

1, first need to install Nodejs mysql package

D:\USER\MYAPPEJS4>NPM install MySQL

[Email protected] Node_modules\mysql

├── [email protected]

├── [email protected]

└── [email protected] ([email protected], [email protected], [email protected]

.0.1, [email protected])

2. Write code that NODEJS interacts with MySQL

Mysql.js

First you need to install the Nodejs mysql package

NPM install MySQL

Write code that NODEJS interacts with MySQL

var mysql = require (' mysql ');

var test_database = ' my_news_test ';

var test_table = ' Node_user ';

Create a connection

var client = Mysql.createconnection ({

User: ' Root ',

password: ' root123 ',

});

Client.connect ();

Client.query ("use" + test_database);

Client.query (

' SELECT * from ' +test_table,

function SELECTCB (err, results, fields) {

if (err) {

throw err;

 } 

if (results)

 {

For (var i = 0; i < results.length; i++)

 {

Console.log ("%d\t%s\t%s", Results[i].id, Results[i].name, results[i].age);

 }

 }   

client.end ();

 } 

);

3. Operation Result

D:\user\myappejs4>node Mysqltest.js

1 admin

2 dans88

3 Zhang San

4 ABCDEF

5 Li Xiao

Iv. node. js combined with MySQL's add, delete, change, check operation

1, increase

var mysql = require (' mysql ');

var connection = Mysql.createconnection ({

host : ' 127.0.0.1 ',

user : ' root ',

password: ' root123 ',

Port: ' 3306 ',

database: ' My_news_test ',

});

Connection.connect ();

var useraddsql = ' INSERT into Node_user (id,name,age) VALUES (0,?,?) ';

var = [' Wilson ', useraddsql_params];

Add added

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.insertid);

console.log (' INSERT ID: ', result);

console.log (' ####################### ');

});

Connection.end ();

Run as follows

D:\user\myappejs4>node Mysqltestadd.js

-------INSERT----------

INSERT ID: {fieldcount:0,

Affectedrows:1,

Insertid:6,

Serverstatus:2,

warningcount:0,

message: ",

Protocol41:true,

changedrows:0}

#######################

2, change

var mysql = require (' mysql ');

var connection = Mysql.createconnection ({

host : ' 127.0.0.1 ',

user : ' root ',

password: ' root123 ',

Port: ' 3306 ',

database: ' My_news_test ',

});

Connection.connect ();

var usermodsql = ' UPDATE node_user SET name =?, age =? WHERE id =? ';

var usermodsql_params = [' Hello world ', 99,7];

Change up

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.affectedrows);

console.log (' ****************************** ');

});

Connection.end ();

The operation results are as follows

D:\user\myappejs4>node Mysqltest_up.js

----------UPDATE-------------

UPDATE AffectedRows 1

******************************

3, check the operation

var mysql = require (' mysql ');

var connection = Mysql.createconnection ({

host : ' 127.0.0.1 ',

user : ' root ',

password: ' root123 ',

Port: ' 3306 ',

database: ' My_news_test ',

});

Connection.connect ();

var usergetsql = ' SELECT * from Node_user ';

Search Query

Connection.query (Usergetsql,function (err, result) {

if (err) {

Console.log (' [SELECT ERROR]-', err.message);

return;

 }       

console.log ('---------------SELECT----------------');

Console.log (Result);

console.log (' $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ ');

});

Connection.end ();

The results of the operation are as follows

D:\user\myappejs4>node Mysqltest_query.js

---------------SELECT----------------

[{id:1, Name: ' Admin ', age:32},

{id:2, Name: ' Dans88 ', age:45},

{id:3, name: ' Zhang San ', age:35},

{id:4, Name: ' ABCDEF ', age:88},

{id:5, name: ' Li Xiao ', age:65},

{id:6, Name: ' Wilson ', age:55}]

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

4. Delete operation

var mysql = require (' mysql ');

var connection = Mysql.createconnection ({

host : ' 127.0.0.1 ',

user : ' root ',

password: ' root123 ',

Port: ' 3306 ',

database: ' My_news_test ',

});

Connection.connect ();

var userdelsql = ' DELETE from node_user WHERE id = 7 ';

//?

Connection.query (Userdelsql,function (err, result) {

if (err) {

Console.log (' [DELETE ERROR]-', err.message);

return;

     }       

console.log ('-------------DELETE--------------');

console.log (' DELETE affectedrows ', result.affectedrows);

console.log (' &&&&&&&&&&&&&&&&& ');

});

Connection.end ();

The results of the operation are as follows

D:\user\myappejs4>node Mysqltest_del.js

-------------DELETE--------------

DELETE AffectedRows 1

&&&&&&&&&&&&&&&&&&&&&& &&&&&&&&&&&&

Add, delete, change, check operation all completed!

Nodejs MySQL database Add, delete, change, check operation