1 MySQL中利用外键实现级联删除、更新 2 3 MySQL支持外键的存储引擎只有InnoDB,在创建外键的时候,要求父表必须有对应的索引,子表在创建外键的时候也会自动创建对应的索引。在创建索引的时候,可以指定在删除、更新父表时,对子表进行的相应操作,包括RESTRICT、NO ACTION、SET NULL和CASCADE。其中RESTRICT和NO ACTION相同,是指在子表有关联记录的情况下父表不能更新;CASCADE表示父表在更新或者删除时,更新或者删除子表对应记录;SET NULL则是表示父表在更新或者删除的时候,子表的对应字段被SET NULL。 4 5 注意: 6 1、存储引擎必须使用InnoDB引擎; 7 2、外键必须建立索引; 8 3、外键绑定关系这里使用了“ ON DELETE CASCADE ” “ON UPDATE CASCADE”,意思是如果外键对应数据被删除或者更新时,将关联数据完全删除或者相应地更新。更多信息请参考MySQL手册中关于InnoDB的文档; 9 10 11 建表时创建外键: 12 CREATE TABLE`xh` ( 13 `id` int(100) unsigned NOT NULL AUTO_INCREMENT COMMENT , 14 `cl_id` smallint(3) unsigned NOT NULL COMMENT, 15 `title` varchar(100) COLLATE utf8_unicode_ci NOT NULL COMMENT , 16 `details` text COLLATE utf8_unicode_ci NOT NULL COMMENT , 17 `date` datetime NOT NULL COMMENT , 18 `au_id` smallint(6) unsigned NOT NULL COMMENT , 19 `click` int(100) unsigned NOT NULL DEFAULT '0' COMMENT , 20 `reco` int(100) unsigned NOT NULL DEFAULT '0' COMMENT, 21 PRIMARY KEY (`id`), 22 KEY `fk_class` (`cl_id`), 23 CONSTRAINT `fk_class`FOREIGN KEY (`cl_id`) REFERENCES `fl` (`id`), 24 KEY `fk_author` (`au_id`), 25 CONSTRAINT `fk_author` FOREIGN KEY (`au_id`) REFERENCES `author` (`id`) ON DELETE CASCADE ON UPDATE CASCADE 26 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 依赖模块: 2 3 mysql:https://github.com/felixge/node-mysql npm install mysql --save 4 async:https://github.com/caolan/async npm install async --save (ps: async模块可换成其它Promise模块如bluebird、q等) 因为Node.js的mysql模块本身对于事务的封装过于简单,而且直接使用会有很严重callback hell,故我们封装了两个方法,一个用来初始化sql & params,一个用来执行事务。 5 6 初始化sql & params: 7 function _getNewSqlParamEntity(sql, params, callback) { 8 if (callback) { 9 return callback(null, { 10 sql: sql, 11 params: params 12 }); 13 } 14 return { 15 sql: sql, 16 params: params 17 }; 18 } 19 20 如果你要执行多条sql语句,则需要: 21 var sqlParamsEntity = []; 22 var sql1 = "insert table set a=?, b=? where 1=1"; 23 var param1 = {a:1, b:2}; 24 sqlParamsEntity.push(_getNewSqlParamEntity(sql1, param1)); 25 26 var sql2 = "update ..."; 27 sqlParamsEntity.push(_getNewSqlParamEntity(sql1, [])); 28 29 //...更多要事务执行的sql 30 31 32 然后我在我自己的dbHelper.js里封装了execTrans的函数,用来执行事务: 33 var mysql = require('mysql'); 34 var async = require("async"); 35 36 module.exports = { 37 execTrans: execTrans, 38 } 39 40 var pool = mysql.createPool({ 41 host: "mysql host", 42 user: "mysql login user", 43 password: "mysql login pwd", 44 database: "target db name", 45 connectionLimit: 10, 46 port: "mysql db port", 47 waitForConnections: false 48 }); 49 50 function execTrans(sqlparamsEntities, callback) { 51 pool.getConnection(function (err, connection) { 52 if (err) { 53 return callback(err, null); 54 } 55 connection.beginTransaction(function (err) { 56 if (err) { 57 return callback(err, null); 58 } 59 console.log("开始执行transaction,共执行" + sqlparamsEntities.length + "条数据"); 60 var funcAry = []; 61 sqlparamsEntities.forEach(function (sql_param) { 62 var temp = function (cb) { 63 var sql = sql_param.sql; 64 var param = sql_param.params; 65 connection.query(sql, param, function (tErr, rows, fields) { 66 if (tErr) { 67 connection.rollback(function () { 68 console.log("事务失败," + sql_param + ",ERROR:" + tErr); 69 throw tErr; 70 }); 71 } else { 72 return cb(null, 'ok'); 73 } 74 }) 75 }; 76 funcAry.push(temp); 77 }); 78 79 async.series(funcAry, function (err, result) { 80 console.log("transaction error: " + err); 81 if (err) { 82 connection.rollback(function (err) { 83 console.log("transaction error: " + err); 84 connection.release(); 85 return callback(err, null); 86 }); 87 } else { 88 connection.commit(function (err, info) { 89 console.log("transaction info: " + JSON.stringify(info)); 90 if (err) { 91 console.log("执行事务失败," + err); 92 connection.rollback(function (err) { 93 console.log("transaction error: " + err); 94 connection.release(); 95 return callback(err, null); 96 }); 97 } else { 98 connection.release(); 99 return callback(null, info); 100 } 101 }) 102 } 103 }) 104 }); 105 }); 106 } 107 108 这样就可以执行事务了: 109 execTrans(sqlParamsEntity, function(err, info){ 110 if(err){ 111 console.error("事务执行失败"); 112 }else{ 113 console.log("done."); 114 } 115 })
1 1.查看数据库编码格式 2 3 1 4 mysql> show variables like 'character_set_database'; 5 2.查看数据表的编码格式 6 7 1 8 mysql> show create table <表名>; 9 3.创建数据库时指定数据库的字符集 10 11 mysql>create database <数据库名> character set utf8; 12 4.创建数据表时指定数据表的编码格式 13 14 create table tb_books ( 15 name varchar(45) not null, 16 price double not null, 17 bookCount int not null, 18 author varchar(45) not null ) default charset = utf8; 19 5.修改数据库的编码格式 20 21 mysql>alter database <数据库名> character set utf8; 22 6.修改数据表格编码格式 23 24 mysql>alter table <表名> character set utf8; 25 7.修改字段编码格式 26 27 mysql>alter table <表名> change <字段名> <字段名> <类型> character set utf8; 28 29 mysql>alter table user change username username varchar(20) character set utf8 not null; 30 8.添加外键 31 32 mysql>alter table tb_product add constraint fk_1 foreign key(factoryid) references tb_factory(factoryid); 33 mysql>alter table <表名> add constraint <外键名> foreign key<字段名> REFERENCES <外表表名><字段名>; 34 9.删除外键 35 36 mysql>alter table tb_people drop foreign key fk_1; 37 mysql>alter table <表名> drop foreign key <外键名>;