NodeJS入门项目案例（Express+Mysql）

# mysql [![NPM Version][npm-image]][npm-url] [![NPM Downloads][downloads-image]][downloads-url] [![Node.js Version][node-version-image]][node-version-url] [![Linux Build][travis-image]][travis-url] [![Windows Build][appveyor-image]][appveyor-url] [![Test Coverage][coveralls-image]][coveralls-url] ## Table of Contents - [Install](#install) - [Introduction](#introduction) - [Contributors](#contributors) - [Sponsors](#sponsors) - [Community](#community) - [Establishing connections](#establishing-connections) - [Connection options](#connection-options) - [SSL options](#ssl-options) - [Terminating connections](#terminating-connections) - [Pooling connections](#pooling-connections) - [Pool options](#pool-options) - [Pool events](#pool-events) - [Closing all the connections in a pool](#closing-all-the-connections-in-a-pool) - [PoolCluster](#poolcluster) - [PoolCluster options](#poolcluster-options) - [Switching users and altering connection state](#switching-users-and-altering-connection-state) - [Server disconnects](#server-disconnects) - [Performing queries](#performing-queries) - [Escaping query values](#escaping-query-values) - [Escaping query identifiers](#escaping-query-identifiers) - [Preparing Queries](#preparing-queries) - [Custom format](#custom-format) - [Getting the id of an inserted row](#getting-the-id-of-an-inserted-row) - [Getting the number of affected rows](#getting-the-number-of-affected-rows) - [Getting the number of changed rows](#getting-the-number-of-changed-rows) - [Getting the connection ID](#getting-the-connection-id) - [Executing queries in parallel](#executing-queries-in-parallel) - [Streaming query rows](#streaming-query-rows) - [Piping results with Streams2](#piping-results-with-streams2) - [Multiple statement queries](#multiple-statement-queries) - [Stored procedures](#stored-procedures) - [Joins with overlapping column names](#joins-with-overlapping-column-names) - [Transactions](#transactions) - [Timeouts](#timeouts) - [Error handling](#error-handling) - [Exception Safety](#exception-safety) - [Type casting](#type-casting) - [Connection Flags](#connection-flags) - [Debugging and reporting problems](#debugging-and-reporting-problems) - [Contributing](#contributing) - [Running tests](#running-tests) - [Todo](#todo) ## Install ```sh $ npm install mysql ``` For information about the previous 0.9.x releases, visit the [v0.9 branch][]. Sometimes I may also ask you to install the latest version from Github to check if a bugfix is working. In this case, please do: ```sh $ npm install mysqljs/mysql ``` [v0.9 branch]: https://github.com/mysqljs/mysql/tree/v0.9 ## Introduction This is a node.js driver for mysql. It is written in JavaScript, does not require compiling, and is 100% MIT licensed. Here is an example on how to use it: ```js var mysql = require('mysql'); var connection = mysql.createConnection({ host : 'localhost', user : 'me', password : 'secret', database : 'my_db' }); connection.connect(); connection.query('SELECT 1 + 1 AS solution', function(err, rows, fields) { if (err) throw err; console.log('The solution is: ', rows[0].solution); }); connection.end(); ``` From this example, you can learn the following: * Every method you invoke on a connection is queued and executed in sequence. * Closing the connection is done using `end()` which makes sure all remaining queries are executed before sending a quit packet to the mysql server. ## Contributors Thanks goes to the people who have contributed code to this module, see the [GitHub Contributors page][]. [GitHub Contributors page]: https://github.com/mysqljs/mysql/graphs/contributors Additionally I'd like to thank the following people: * [Andrey Hristov][] (Oracle) - for helping me with protocol questions. * [Ulf Wendel][] (Oracle) - for helping me with protocol questions. [Ulf Wendel]: http://blog.ulf-wendel.de/ [Andrey Hristov]: http://andrey.hristov.com/ ## Sponsors The following companies have supported this project financially, allowing me to spend more time on it (ordered by time of contribution): * [Transloadit](http://transloadit.com) (my startup, we do file uploading & video encoding as a service, check it out) * [Joyent](http://www.joyent.com/) * [pinkbike.com](http://pinkbike.com/) * [Holiday Extras](http://www.holidayextras.co.uk/) (they are [hiring](http://join.holidayextras.co.uk/)) * [Newscope](http://newscope.com/) (they are [hiring](http://www.newscope.com/stellenangebote)) ## Community If you'd like to discuss this module, or ask questions about it, please use one of the following: * **Mailing list**: https://groups.google.com/forum/#!forum/node-mysql * **IRC Channel**: #node.js (on freenode.net, I pay attention to any message including the term `mysql`) ## Establishing connections The recommended way to establish a connection is this: ```js var mysql = require('mysql'); var connection = mysql.createConnection({ host : 'example.org', user : 'bob', password : 'secret' }); connection.connect(function(err) { if (err) { console.error('error connecting: ' + err.stack); return; } console.log('connected as id ' + connection.threadId); }); ``` However, a connection can also be implicitly established by invoking a query: ```js var mysql = require('mysql'); var connection = mysql.createConnection(...); connection.query('SELECT 1', function(err, rows) { // connected! (unless `err` is set) }); ``` Depending on how you like to handle your errors, either method may be appropriate. Any type of connection error (handshake or network) is considered a fatal error, see the [Error Handling](#error-handling) section for more information. ## Connection options When establishing a connection, you can set the following options: * `host`: The hostname of the database you are connecting to. (Default: `localhost`) * `port`: The port number to connect to. (Default: `3306`) * `localAddress`: The source IP address to use for TCP connection. (Optional) * `socketPath`: The path to a unix domain socket to connect to. When used `host` and `port` are ignored. * `user`: The MySQL user to authenticate as. * `password`: The password of that MySQL user. * `database`: Name of the database to use for this connection (Optional). * `charset`: The charset for the connection. This is called "collation" in the SQL-level of MySQL (like `utf8_general_ci`). If a SQL-level charset is specified (like `utf8mb4`) then the default collation for that charset is used. (Default: `'UTF8_GENERAL_CI'`) * `timezone`: The timezone used to store local dates. (Default: `'local'`) * `connectTimeout`: The milliseconds before a timeout occurs during the initial connection to the MySQL server. (Default: `10000`) * `stringifyObjects`: Stringify objects instead of converting to values. See issue [#501](https://github.com/mysqljs/mysql/issues/501). (Default: `false`) * `insecureAuth`: Allow connecting to MySQL instances that ask for the old (insecure) authentication method. (Default: `false`) * `typeCast`: Determines if column values should be converted to native JavaScript types. (Default: `true`) * `queryFormat`: A custom query format function. See [Custom format](#custom-format). * `supportBigNumbers`: When dealing with big numbers (BIGINT and DECIMAL columns) in the database, you should enable this option (Default: `false`). * `bigNumberStrings`: Enabling both `supportBigNumbers` and `bigNumberStrings` forces big numbers (BIGINT and DECIMAL columns) to be always returned as JavaScript String objects (Default: `false`). Enabling `supportBigNumbers` but leaving `bigNumberStrings` disabled will return big numbers as String objects only when they cannot be accurately represented with [JavaScript Number objects] (http://ecma262-5.com/ELS5_HTML.htm#Section_8.5) (which happens when they exceed the [-2^53, +2^53] range), otherwise they will be returned as Number objects. This option is ignored