适用于高版本8.0(而不适用于低版本的5.0)
# 连接数据库
var mysqlx = require('@mysql/xdevapi');
// Connect to server on localhost
mysqlx
.getSession({
user: 'user',
password: 'password',
host: 'localhost',
port: '33060'
})
.then(function (session) {
var db = session.getSchema('test');
// Use the collection 'my_collection'
var myColl = db.getCollection('my_collection');
// Specify which document to find with Collection.find() and
// fetch it from the database with .execute()
return myColl
.find('name like :param')
.limit(1)
.bind('param', 'L%')
.execute(function (doc) {
console.log(doc);
});
})
.catch(function (err) {
// Handle error
});
# 使用SQL与Session会话
var mysqlx = require('@mysql/xdevapi');
var session;
// Connect to server using a Low-Level Session
mysqlx
.getSession('user:password@localhost')
.then(function (s) {
session = s;
return session.getSchema('test');
})
.then(function () {
return Promise.all([
// Switch to use schema 'test'
session.sql('USE test').execute(),
// In a Session context the full SQL language can be used
session.sql('CREATE PROCEDURE my_add_one_procedure' +
' (INOUT incr_param INT) ' +
'BEGIN ' +
' SET incr_param = incr_param + 1;' +
'END;').execute(),
session.executeSql('SET @my_var = ?;', 10).execute(),
session.sql('CALL my_add_one_procedure(@my_var);').execute(),
session.sql('DROP PROCEDURE my_add_one_procedure;').execute()
])
})
.then(function() {
// Use an SQL query to get the result
return session.sql('SELECT @my_var').execute(function (row) {
// Print result
console.log(row);
});
});
# 动态SQL
var mysqlx = require('mysqlx');
function createTestTable(session, name) {
var create = 'CREATE TABLE ';
create += name;
create += ' (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT)';
return session
.sql('DROP TABLE IF EXISTS ' + name)
.execute()
.then(function () {
return session.sql(create).execute();
});
}
var session;
mysqlx
.getSession({
user: 'user',
password: 'password'
})
.then(function (s) {
session = s;
return session
.sql('use myschema')
.execute()
})
.then(function () {
// Creates some tables
return Promise.map([
createTestTable(session, 'test1'),
createTestTable(session, 'test2')
])
})
.then(function () {
session.close();
})
});
# 集合的CRUD操作
// --------------------------------------------------
// Connecting to MySQL Server and working with a Collection
var mysqlx = require('@mysql/xdevapi');
var db;
// Connect to server
mysqlx
.getSession({
user: 'user',
password: 'password',
host: 'localhost',
port: '33060',
})
.then(function (session) {
db = session.getSchema('test');
// Create a new collection 'my_collection'
return db.createCollection('my_collection');
})
.then(function (myColl) {
// Insert documents
return Promise
.all([
myColl.add({ name: 'Laurie', age: 19 }).execute(),
myColl.add({ name: 'Nadya', age: 54 }).execute(),
myColl.add({ name: 'Lukas', age: 32 }).execute()
])
.then(function () {
// Find a document
return myColl
.find('name like :name && age < :age')
.bind({ name: 'L%', age: 20 })
.limit(1)
.execute(function (doc) {
// Print document
console.log(doc);
});
});
})
.then(function(docs) {
// Drop the collection
return db.dropCollection('my_collection');
})
.catch(function(err) {
// Handle error
});