当前位置：Gxlcms > 数据库问题 > Sequelize 和 MySQL 对照

Sequelize 和 MySQL 对照

时间：2021-07-01 10:21:17 帮助过：13人阅读

基本上，Sequelize的操作都会返回一个Promise，在co的框架里面可以直接进行yield，非常方便。

建立数据库连接

  1. <code class="javascript"><span class="hljs-keyword">var sequelize = <span class="hljs-keyword">new Sequelize(
  2.     <span class="hljs-string">‘sample‘, <span class="hljs-comment">// 数据库名
  3.     <span class="hljs-string">‘root‘,   <span class="hljs-comment">// 用户名
  4.     <span class="hljs-string">‘zuki‘,   <span class="hljs-comment">// 用户密码
  5.     {
  6.         <span class="hljs-string">‘dialect‘: <span class="hljs-string">‘mysql‘,  <span class="hljs-comment">// 数据库使用mysql
  7.         <span class="hljs-string">‘host‘: <span class="hljs-string">‘localhost‘, <span class="hljs-comment">// 数据库服务器ip
  8.         <span class="hljs-string">‘port‘: <span class="hljs-number">3306,        <span class="hljs-comment">// 数据库服务器端口
  9.         <span class="hljs-string">‘define‘: {
  10.             <span class="hljs-comment">// 字段以下划线（_）来分割（默认是驼峰命名风格）
  11.             <span class="hljs-string">‘underscored‘: <span class="hljs-literal">true
  12.         }
  13.     }
  14. );</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

定义单张表

Sequelize：

  1. <code class="javascript"><span class="hljs-keyword">var User = sequelize.define(
  2.     <span class="hljs-comment">// 默认表名（一般这里写单数），生成时会自动转换成复数形式
  3.     <span class="hljs-comment">// 这个值还会作为访问模型相关的模型时的属性名，所以建议用小写形式
  4.     <span class="hljs-string">‘user‘,
  5.     <span class="hljs-comment">// 字段定义（主键、created_at、updated_at默认包含，不用特殊定义）
  6.     {
  7.         <span class="hljs-string">‘emp_id‘: {
  8.             <span class="hljs-string">‘type‘: Sequelize.CHAR(<span class="hljs-number">10), <span class="hljs-comment">// 字段类型
  9.             <span class="hljs-string">‘allowNull‘: <span class="hljs-literal">false,         <span class="hljs-comment">// 是否允许为NULL
  10.             <span class="hljs-string">‘unique‘: <span class="hljs-literal">true              <span class="hljs-comment">// 字段是否UNIQUE
  11.         },
  12.         <span class="hljs-string">‘nick‘: {
  13.             <span class="hljs-string">‘type‘: Sequelize.CHAR(<span class="hljs-number">10),
  14.             <span class="hljs-string">‘allowNull‘: <span class="hljs-literal">false
  15.         },
  16.         <span class="hljs-string">‘department‘: {
  17.             <span class="hljs-string">‘type‘: Sequelize.STRING(<span class="hljs-number">64),
  18.             <span class="hljs-string">‘allowNull‘: <span class="hljs-literal">true
  19.         }
  20.     }
  21. );
  22. </span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

SQL：

  1. <code class="sql"><span class="hljs-keyword">CREATE <span class="hljs-keyword">TABLE <span class="hljs-keyword">IF <span class="hljs-keyword">NOT <span class="hljs-keyword">EXISTS <span class="hljs-string">`users` (
  2.     <span class="hljs-string">`id` <span class="hljs-built_in">INTEGER <span class="hljs-keyword">NOT <span class="hljs-literal">NULL auto_increment , 
  3.     <span class="hljs-string">`emp_id` <span class="hljs-built_in">CHAR(<span class="hljs-number">10) <span class="hljs-keyword">NOT <span class="hljs-literal">NULL <span class="hljs-keyword">UNIQUE, 
  4.     <span class="hljs-string">`nick` <span class="hljs-built_in">CHAR(<span class="hljs-number">10) <span class="hljs-keyword">NOT <span class="hljs-literal">NULL, 
  5.     <span class="hljs-string">`department` <span class="hljs-built_in">VARCHAR(<span class="hljs-number">64),
  6.     <span class="hljs-string">`created_at` DATETIME <span class="hljs-keyword">NOT <span class="hljs-literal">NULL, 
  7.     <span class="hljs-string">`updated_at` DATETIME <span class="hljs-keyword">NOT <span class="hljs-literal">NULL, 
  8.     <span class="hljs-keyword">PRIMARY <span class="hljs-keyword">KEY (<span class="hljs-string">`id`)
  9. ) <span class="hljs-keyword">ENGINE=<span class="hljs-keyword">InnoDB;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

几点说明：

  1. 建表SQL会自动执行的意思是你主动调用sync的时候。类似这样：User.sync({force: true});（加force:true，会先删掉表后再建表）。我们也可以先定义好表结构，再来定义Sequelize模型，这时可以不用sync。两者在定义阶段没有什么关系，直到我们真正开始操作模型时，才会触及到表的操作，但是我们当然还是要尽量保证模型和表的同步（可以借助一些migration工具）。自动建表功能有风险，使用需谨慎。

  2. 所有数据类型，请参考文档数据类型。

  3. 模型还可以定义虚拟属性、类方法、实例方法，请参考文档：模型定义

  4. 其他一些特殊定义如下所示：

  1. <code class="javascript"><span class="hljs-keyword">var User = sequelize.define(
  2.     <span class="hljs-string">‘user‘,
  3.     {
  4.         <span class="hljs-string">‘emp_id‘: {
  5.             <span class="hljs-string">‘type‘: Sequelize.CHAR(<span class="hljs-number">10), <span class="hljs-comment">// 字段类型
  6.             <span class="hljs-string">‘allowNull‘: <span class="hljs-literal">false,         <span class="hljs-comment">// 是否允许为NULL
  7.             <span class="hljs-string">‘unique‘: <span class="hljs-literal">true              <span class="hljs-comment">// 字段是否UNIQUE
  8.         },
  9.         <span class="hljs-string">‘nick‘: {
  10.             <span class="hljs-string">‘type‘: Sequelize.CHAR(<span class="hljs-number">10),
  11.             <span class="hljs-string">‘allowNull‘: <span class="hljs-literal">false
  12.         },
  13.         <span class="hljs-string">‘department‘: {
  14.             <span class="hljs-string">‘type‘: Sequelize.STRING(<span class="hljs-number">64),
  15.             <span class="hljs-string">‘allowNull‘: <span class="hljs-literal">true
  16.         }
  17.     },
  18.     {
  19.         <span class="hljs-comment">// 自定义表名
  20.         <span class="hljs-string">‘freezeTableName‘: <span class="hljs-literal">true,
  21.         <span class="hljs-string">‘tableName‘: <span class="hljs-string">‘xyz_users‘,
  22.         <span class="hljs-comment">// 是否需要增加createdAt、updatedAt、deletedAt字段
  23.         <span class="hljs-string">‘timestamps‘: <span class="hljs-literal">true,
  24.         <span class="hljs-comment">// 不需要createdAt字段
  25.         <span class="hljs-string">‘createdAt‘: <span class="hljs-literal">false,
  26.         <span class="hljs-comment">// 将updatedAt字段改个名
  27.         <span class="hljs-string">‘updatedAt‘: <span class="hljs-string">‘utime‘
  28.         <span class="hljs-comment">// 将deletedAt字段改名
  29.         <span class="hljs-comment">// 同时需要设置paranoid为true（此种模式下，删除数据时不会进行物理删除，而是设置deletedAt为当前时间
  30.         <span class="hljs-string">‘deletedAt‘: <span class="hljs-string">‘dtime‘,
  31.         <span class="hljs-string">‘paranoid‘: <span class="hljs-literal">true
  32.     }
  33. );</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

单表增删改查

通过Sequelize获取的模型对象都是一个DAO（Data Access Object）对象，这些对象会拥有许多操作数据库表的实例对象方法（比如：save、update、destroy等），需要获取“干净”的JSON对象可以调用get({‘plain‘: true})。

通过模型的类方法可以获取模型对象（比如：findById、findAll等）。

增

Sequelize：

  1. <code class="javascript"><span class="hljs-comment">// 方法1：build后对象只存在于内存中，调用save后才操作db
  2. <span class="hljs-keyword">var user = User.build({
  3.     <span class="hljs-string">‘emp_id‘: <span class="hljs-string">‘1‘,
  4.     <span class="hljs-string">‘nick‘: <span class="hljs-string">‘小红‘,
  5.     <span class="hljs-string">‘department‘: <span class="hljs-string">‘技术部‘
  6. });
  7. user = <span class="hljs-keyword">yield user.save();
  8. <span class="hljs-built_in">console.log(user.get({<span class="hljs-string">‘plain‘: <span class="hljs-literal">true}));
  9. <span class="hljs-comment">// 方法2：直接操作db
  10. <span class="hljs-keyword">var user = <span class="hljs-keyword">yield User.create({
  11.     <span class="hljs-string">‘emp_id‘: <span class="hljs-string">‘2‘,
  12.     <span class="hljs-string">‘nick‘: <span class="hljs-string">‘小明‘,
  13.     <span class="hljs-string">‘department‘: <span class="hljs-string">‘技术部‘
  14. });
  15. <span class="hljs-built_in">console.log(user.get({<span class="hljs-string">‘plain‘: <span class="hljs-literal">true}));</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

SQL：

  1. <code class="sql"><span class="hljs-keyword">INSERT <span class="hljs-keyword">INTO <span class="hljs-string">`users` 
  2. (<span class="hljs-string">`id`, <span class="hljs-string">`emp_id`, <span class="hljs-string">`nick`, <span class="hljs-string">`department`, <span class="hljs-string">`updated_at`, <span class="hljs-string">`created_at`) 
  3. <span class="hljs-keyword">VALUES 
  4. (<span class="hljs-keyword">DEFAULT, <span class="hljs-string">‘1‘, <span class="hljs-string">‘小红‘, <span class="hljs-string">‘技术部‘, <span class="hljs-string">‘2015-11-02 14:49:54‘, <span class="hljs-string">‘2015-11-02 14:49:54‘);</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

Sequelize会为主键id设置DEFAULT值来让数据库产生自增值，还将当前时间设置成了created_at和updated_at字段，非常方便。

改

Sequelize：

  1. <code class="javascript"><span class="hljs-comment">// 方法1：操作对象属性（不会操作db），调用save后操作db
  2. user.nick = <span class="hljs-string">‘小白‘;
  3. user = <span class="hljs-keyword">yield user.save();
  4. <span class="hljs-built_in">console.log(user.get({<span class="hljs-string">‘plain‘: <span class="hljs-literal">true}));
  5. <span class="hljs-comment">// 方法2：直接update操作db
  6. user = <span class="hljs-keyword">yield user.update({
  7.     <span class="hljs-string">‘nick‘: <span class="hljs-string">‘小白白‘
  8. });
  9. <span class="hljs-built_in">console.log(user.get({<span class="hljs-string">‘plain‘: <span class="hljs-literal">true}));</span></span></span></span></span></span></span></span></span></span></span></span></span></code>

SQL：

  1. <code class="sql"><span class="hljs-keyword">UPDATE <span class="hljs-string">`users` 
  2. <span class="hljs-keyword">SET <span class="hljs-string">`nick` = <span class="hljs-string">‘小白白‘, <span class="hljs-string">`updated_at` = <span class="hljs-string">‘2015-11-02 15:00:04‘ 
  3. <span class="hljs-keyword">WHERE <span class="hljs-string">`id` = <span class="hljs-number">1;</span></span></span></span></span></span></span></span></span></span></code>

更新操作时，Sequelize将将当前时间设置成了updated_at，非常方便。

如果想限制更新属性的白名单，可以这样写：

  1. <code class="javascript"><span class="hljs-comment">// 方法1
  2. user.emp_id = <span class="hljs-string">‘33‘;
  3. user.nick = <span class="hljs-string">‘小白‘;
  4. user = <span class="hljs-keyword">yield user.save({<span class="hljs-string">‘fields‘: [<span class="hljs-string">‘nick‘]});
  5. <span class="hljs-comment">// 方法2
  6. user = <span class="hljs-keyword">yield user.update(
  7.     {<span class="hljs-string">‘emp_id‘: <span class="hljs-string">‘33‘, <span class="hljs-string">‘nick‘: <span class="hljs-string">‘小白‘},
  8.     {<span class="hljs-string">‘fields‘: [<span class="hljs-string">‘nick‘]}
  9. });</span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

这样就只会更新nick字段，而emp_id会被忽略。这种方法在对表单提交过来的一大推数据中只更新某些属性的时候比较有用。

删

Sequelize：

  1. <code class="javascript"><span class="hljs-keyword">yield user.destroy();</span></code>

SQL：

  1. <code class="sql"><span class="hljs-keyword">DELETE <span class="hljs-keyword">FROM <span class="hljs-string">`users` <span class="hljs-keyword">WHERE <span class="hljs-string">`id` = <span class="hljs-number">1;</span></span></span></span></span></span></code>

这里有个特殊的地方是，如果我们开启了paranoid（偏执）模式，destroy的时候不会执行DELETE语句，而是执行一个UPDATE语句将deleted_at字段设置为当前时间（一开始此字段值为NULL）。我们可以使用user.destroy({force: true})来强制删除，从而执行DELETE语句进行物理删除。

查

查全部

Sequelize：

  1. <code class="javascript"><span class="hljs-keyword">var users = <span class="hljs-keyword">yield User.findAll();
  2. <span class="hljs-built_in">console.log(users);</span></span></span></code>

SQL：

  1. <code class="sql"><span class="hljs-keyword">SELECT <span class="hljs-string">`id`, <span class="hljs-string">`emp_id`, <span class="hljs-string">`nick`, <span class="hljs-string">`department`, <span class="hljs-string">`created_at`, <span class="hljs-string">`updated_at` <span class="hljs-keyword">FROM <span class="hljs-string">`users`;</span></span></span></span></span></span></span></span></span></code>

限制字段

Sequelize：

  1. <code class="javascript"><span class="hljs-keyword">var users = <span class="hljs-keyword">yield User.findAll({
  2.     <span class="hljs-string">‘attributes‘: [<span class="hljs-string">‘emp_id‘, <span class="hljs-string">‘nick‘]
  3. });
  4. <span class="hljs-built_in">console.log(users);</span></span></span></span></span></span></code>

SQL：

  1. <code class="sql"><span class="hljs-keyword">SELECT <span class="hljs-string">`emp_id`, <span class="hljs-string">`nick` <span class="hljs-keyword">FROM <span class="hljs-string">`users`;</span></span></span></span></span></code>

字段重命名

Sequelize：

  1. <code class="javascript"><span class="hljs-keyword">var users = <span class="hljs-keyword">yield User.findAll({
  2.     <span class="hljs-string">‘attributes‘: [
  3.         <span class="hljs-string">‘emp_id‘, [<span class="hljs-string">‘nick‘, <span class="hljs-string">‘user_nick‘]
  4.     ]
  5. });
  6. <span class="hljs-built_in">console.log(users);</span></span></span></span></span></span></span></code>

SQL：

  1. <code class="sql"><span class="hljs-keyword">SELECT <span class="hljs-string">`emp_id`, <span class="hljs-string">`nick` <span class="hljs-keyword">AS <span class="hljs-string">`user_nick` <span class="hljs-keyword">FROM <span class="hljs-string">`users`;</span></span></span></span></span></span></span></code>

where子句

Sequelize的where配置项基本上完全支持了SQL的where子句的功能，非常强大。我们一步步来进行介绍。

基本条件

Sequelize：

  1. <code class="javascript"><span class="hljs-keyword">var users = <span class="hljs-keyword">yield User.findAll({
  2.     <span class="hljs-string">‘where‘: {
  3.         <span class="hljs-string">‘id‘: [<span class="hljs-number">1, <span class="hljs-number">2, <span class="hljs-number">3],
  4.         <span class="hljs-string">‘nick‘: <span class="hljs-string">‘a‘,
  5.         <span class="hljs-string">‘department‘: <span class="hljs-literal">null
  6.     }
  7. });
  8. <span class="hljs-built_in">console.log(users);</span></span></span></span></span></span></span></span></span></span></span></span></code>

SQL：

  1. <code class="sql"><span class="hljs-keyword">SELECT <span class="hljs-string">`id`, <span class="hljs-string">`emp_id`, <span class="hljs-string">`nick`, <span class="hljs-string">`department`, <span class="hljs-string">`created_at`, <span class="hljs-string">`updated_at` 
  2. <span class="hljs-keyword">FROM <span class="hljs-string">`users` <span class="hljs-keyword">AS <span class="hljs-string">`user` 
  3. <span class="hljs-keyword">WHERE 
  4.     <span class="hljs-string">`user`.<span class="hljs-string">`id` <span class="hljs-keyword">IN (<span class="hljs-number">1, <span class="hljs-number">2, <span class="hljs-number">3) <span class="hljs-keyword">AND 
  5.     <span class="hljs-string">`user`.<span class="hljs-string">`nick`=<span class="hljs-string">‘a‘ <span class="hljs-keyword">AND 
  6.     <span class="hljs-string">`user`.<span class="hljs-string">`department` <span class="hljs-keyword">IS <span class="hljs-literal">NULL;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

可以看到，k: v被转换成了k = v，同时一个对象的多个k: v对被转换成了AND条件，即：k1: v1, k2: v2转换为k1 = v1 AND k2 = v2。

这里有2个要点：

  • 如果v是null，会转换为IS NULL（因为SQL没有= NULL
    这种语法）

  • 如果v是数组，会转换为IN条件（因为SQL没有=[1,2,3]这种语法，况且也没数组这种类型）

操作符

操作符是对某个字段的进一步约束，可以有多个（对同一个字段的多个操作符会被转化为AND）。

Sequelize：

  1. <code class="javascript"><span class="hljs-keyword">var users = <span class="hljs-keyword">yield User.findAll({
  2.     <span class="hljs-string">‘where‘: {
  3.         <span class="hljs-string">‘id‘: {
  4.             <span class="hljs-string">‘$eq‘: <span class="hljs-number">1,                <span class="hljs-comment">// id = 1
  5.             <span class="hljs-string">‘$ne‘: <span class="hljs-number">2,                <span class="hljs-comment">// id != 2
  6.             <span class="hljs-string">‘$gt‘: <span class="hljs-number">6,                <span class="hljs-comment">// id > 6
  7.             <span class="hljs-string">‘$gte‘: <span class="hljs-number">6,               <span class="hljs-comment">// id >= 6
  8.             <span class="hljs-string">‘$lt‘: <span class="hljs-number">10,               <span class="hljs-comment">// id < 10
  9.             <span class="hljs-string">‘$lte‘: <span class="hljs-number">10,              <span class="hljs-comment">// id <= 10
  10.             <span class="hljs-string">‘$between‘: [<span class="hljs-number">6, <span class="hljs-number">10],     <span class="hljs-comment">// id BETWEEN 6 AND 10
  11.             <span class="hljs-string">‘$notBetween‘: [<span class="hljs-number">11, <span class="hljs-number">15], <span class="hljs-comment">// id NOT BETWEEN 11 AND 15
  12.             <span class="hljs-string">‘$in‘: [<span class="hljs-number">1, <span class="hljs-number">2],           <span class="hljs-comment">// id IN (1, 2)
  13.             <span class="hljs-string">‘$notIn‘: [<span class="hljs-number">3, <span class="hljs-number">4]         <span class="hljs-comment">// id NOT IN (3, 4)
  14.         },
  15.         <span class="hljs-string">‘nick‘: {
  16.             <span class="hljs-string">‘$like‘: <span class="hljs-string">‘%a%‘,          <span class="hljs-comment">// nick LIKE ‘%a%‘
  17.             <span class="hljs-string">‘$notLike‘: <span class="hljs-string">‘%a‘         <span class="hljs-comment">// nick NOT LIKE ‘%a‘
  18.         },
  19.         <span class="hljs-string">‘updated_at‘: {
  20.             <span class="hljs-string">‘$eq‘: <span class="hljs-literal">null,             <span class="hljs-comment">// updated_at IS NULL
  21.             <span class="hljs-string">‘$ne‘: <span class="hljs-literal">null              <span class="hljs-comment">// created_at IS NOT NULL
  22.         }
  23.     }
  24. });
  25. </span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

SQL：

  1. <code class="sql"><span class="hljs-keyword">SELECT <span class="hljs-string">`id`, <span class="hljs-string">`emp_id`, <span class="hljs-string">`nick`, <span class="hljs-string">`department`, <span class="hljs-string">`created_at`, <span class="hljs-string">`updated_at` 
  2. <span class="hljs-keyword">FROM <span class="hljs-string">`users` <span class="hljs-keyword">AS <span class="hljs-string">`user` 
  3. <span class="hljs-keyword">WHERE 
  4. (
  5.     <span class="hljs-string">`user`.<span class="hljs-string">`id` = <span class="hljs-number">1 <span class="hljs-keyword">AND 
  6.     <span class="hljs-string">`user`.<span class="hljs-string">`id` != <span class="hljs-number">2 <span class="hljs-keyword">AND 
  7.     <span class="hljs-string">`user`.<span class="hljs-string">`id` > <span class="hljs-number">6 <span class="hljs-keyword">AND 
  8.     <span class="hljs-string">`user`.<span class="hljs-string">`id` >= <span class="hljs-number">6 <span class="hljs-keyword">AND 
  9.     <span class="hljs-string">`user`.<span class="hljs-string">`id` < <span class="hljs-number">10 <span class="hljs-keyword">AND 
  10.     <span class="hljs-string">`user`.<span class="hljs-string">`id` <= <span class="hljs-number">10 <span class="hljs-keyword">AND 
  11.     <span class="hljs-string">`user`.<span class="hljs-string">`id` <span class="hljs-keyword">BETWEEN <span class="hljs-number">6 <span class="hljs-keyword">AND <span class="hljs-number">10 <span class="hljs-keyword">AND 
  12.     <span class="hljs-string">`user`.<span class="hljs-string">`id` <span class="hljs-keyword">NOT <span class="hljs-keyword">BETWEEN <span class="hljs-number">11 <span class="hljs-keyword">AND <span class="hljs-number">15 <span class="hljs-keyword">AND
  13.     <span class="hljs-string">`user`.<span class="hljs-string">`id` <span class="hljs-keyword">IN (<span class="hljs-number">1, <span class="hljs-number">2) <span class="hljs-keyword">AND 
  14.     <span class="hljs-string">`user`.<span class="hljs-string">`id` <span class="hljs-keyword">NOT <span class="hljs-keyword">IN (<span class="hljs-number">3, <span class="hljs-number">4)
  15. ) 
  16. <span class="hljs-keyword">AND 
  17. (
  18.     <span class="hljs-string">`user`.<span class="hljs-string">`nick` <span class="hljs-keyword">LIKE <span class="hljs-string">‘%a%‘ <span class="hljs-keyword">AND 
  19.     <span class="hljs-string">`user`.<span class="hljs-string">`nick` <span class="hljs-keyword">NOT <span class="hljs-keyword">LIKE <span class="hljs-string">‘%a‘
  20. ) 
  21. <span class="hljs-keyword">AND 
  22. (
  23.     <span class="hljs-string">`user`.<span class="hljs-string">`updated_at` <span class="hljs-keyword">IS <span class="hljs-literal">NULL <span class="hljs-keyword">AND 
  24.     <span class="hljs-string">`user`.<span class="hljs-string">`updated_at` <span class="hljs-keyword">IS <span class="hljs-keyword">NOT <span class="hljs-literal">NULL
  25. );</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

这里我们发现，其实相等条件k: v这种写法是操作符写法k: {$eq: v}的简写。而要实现不等条件就必须使用操作符写法k: {$ne: v}。

条件

上面我们说的条件查询，都是AND查询，Sequelize同时也支持OR、NOT、甚至多种条件的联合查询。

AND条件

Sequelize：

  1. <code class="javascript"><span class="hljs-keyword">var users = <span class="hljs-keyword">yield User.findAll({
  2.     <span class="hljs-string">‘where‘: {
  3.         <span class="hljs-string">‘$and‘: [
  4.             {<span class="hljs-string">‘id‘: [<span class="hljs-number">1, <span class="hljs-number">2]},
  5.             {<span class="hljs-string">‘nick‘: <span class="hljs-literal">null}
  6.         ]
  7.     }
  8. });</span></span></span></span></span></span></span></span></span></code>

SQL：

  1. <code class="sql"><span class="hljs-keyword">SELECT <span class="hljs-string">`id`, <span class="hljs-string">`emp_id`, <span class="hljs-string">`nick`, <span class="hljs-string">`department`, <span class="hljs-string">`created_at`, <span class="hljs-string">`updated_at` 
  2. <span class="hljs-keyword">FROM <span class="hljs-string">`users` <span class="hljs-keyword">AS <span class="hljs-string">`user` 
  3. <span class="hljs-keyword">WHERE 
  4. (
  5.     <span class="hljs-string">`user`.<span class="hljs-string">`id` <span class="hljs-keyword">IN (<span class="hljs-number">1, <span class="hljs-number">2) <span class="hljs-keyword">AND 
  6.     <span class="hljs-string">`user`.<span class="hljs-string">`nick` <span class="hljs-keyword">IS <span class="hljs-literal">NULL
  7. );</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>
OR条件

Sequelize：

  1. <code class="javascript"><span class="hljs-keyword">var users = <span class="hljs-keyword">yield User.findAll({
  2.     <span class="hljs-string">‘where‘: {
  3.         <span class="hljs-string">‘$or‘: [
  4.             {<span class="hljs-string">‘id‘: [<span class="hljs-number">1, <span class="hljs-number">2]},
  5.             {<span class="hljs-string">‘nick‘: <span class="hljs-literal">null}
  6.         ]
  7.     }
  8. });</span></span></span></span></span></span></span></span></span></code>

SQL：

  1. <code class="sql"><span class="hljs-keyword">SELECT <span class="hljs-string">`id`, <span class="hljs-string">`emp_id`, <span class="hljs-string">`nick`, <span class="hljs-string">`department`, <span class="hljs-string">`created_at`, <span class="hljs-string">`updated_at` 
  2. <span class="hljs-keyword">FROM <span class="hljs-string">`users` <span class="hljs-keyword">AS <span class="hljs-string">`user` 
  3. <span class="hljs-keyword">WHERE 
  4. (
  5.     <span class="hljs-string">`user`.<span class="hljs-string">`id` <span class="hljs-keyword">IN (<span class="hljs-number">1, <span class="hljs-number">2) <span class="hljs-keyword">OR 
  6.     <span class="hljs-string">`user`.<span class="hljs-string">`nick` <span class="hljs-keyword">IS <span class="hljs-literal">NULL
  7. );</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>
NOT条件

Sequelize：

  1. <code class="javascript"><span class="hljs-keyword">var users = <span class="hljs-keyword">yield User.findAll({
  2.     <span class="hljs-string">‘where‘: {
  3.         <span class="hljs-string">‘$not‘: [
  4.             {<span class="hljs-string">‘id‘: [<span class="hljs-number">1, <span class="hljs-number">2]},
  5.             {<span class="hljs-string">‘nick‘: <span class="hljs-literal">null}
  6.         ]
  7.     }
  8. });</span></span></span></span></span></span></span></span></span></code>

SQL：

  1. <code class="sql"><span class="hljs-keyword">SELECT <span class="hljs-string">`id`, <span class="hljs-string">`emp_id`, <span class="hljs-string">`nick`, <span class="hljs-string">`department`, <span class="hljs-string">`created_at`, <span class="hljs-string">`updated_at` 
  2. <span class="hljs-keyword">FROM <span class="hljs-string">`users` <span class="hljs-keyword">AS <span class="hljs-string">`user` 
  3. <span class="hljs-keyword">WHERE 
  4. <span class="hljs-keyword">NOT (
  5.     <span class="hljs-string">`user`.<span class="hljs-string">`id` <span class="hljs-keyword">IN (<span class="hljs-number">1, <span class="hljs-number">2) <span class="hljs-keyword">AND 
  6.     <span class="hljs-string">`user`.<span class="hljs-string">`nick` <span class="hljs-keyword">IS <span class="hljs-literal">NULL
  7. );</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

转换规则

我们这里做个总结。Sequelize对where配置的转换规则的伪代码大概如下：

  1. <code class="javascript"><span class="hljs-function"><span class="hljs-keyword">function <span class="hljs-title">translate<span class="hljs-params">(where) {
  2.     <span class="hljs-keyword">for (k, v of where) {
  3.         <span class="hljs-keyword">if (k == 表字段) {
  4.             <span class="hljs-comment">// 先统一转为操作符形式
  5.             <span class="hljs-keyword">if (v == 基本值) { <span class="hljs-comment">// k: ‘xxx‘
  6.                 v = {<span class="hljs-string">‘$eq‘: v};
  7.             }
  8.             <span class="hljs-keyword">if (v == 数组) { <span class="hljs-comment">// k: [1, 2, 3]
  9.                 v = {<span class="hljs-string">‘$in‘: v};
  10.             }
  11.             <span class="hljs-comment">// 操作符转换
  12.             <span class="hljs-keyword">for (opk, opv of v) {
  13.                 <span class="hljs-comment">// op将opk转换对应的SQL表示
  14.                 => k + op(opk, opv) + AND; 
  15.             }
  16.         }
  17.         <span class="hljs-comment">// 逻辑操作符处理
  18.         <span class="hljs-keyword">if (k == <span class="hljs-string">‘$and‘) {
  19.             <span class="hljs-keyword">for (item <span class="hljs-keyword">in v) {
  20.                 => translate(item) + AND;
  21.             }
  22.         }
  23.         <span class="hljs-keyword">if (k == <span class="hljs-string">‘$or‘) {
  24.             <span class="hljs-keyword">for (item <span class="hljs-keyword">in v) {
  25.                 => translate(item) + OR;
  26.             }
  27.         }
  28.         <span class="hljs-keyword">if (k == <span class="hljs-string">‘$not‘) {
  29.             NOT +
  30.             <span class="hljs-keyword">for (item <span class="hljs-keyword">in v) {
  31.                 => translate(item) + AND;
  32.             }
  33.         }
  34.     }
  35.     <span class="hljs-function"><span class="hljs-keyword">function <span class="hljs-title">op<span class="hljs-params">(opk, opv) {
  36.         <span class="hljs-keyword">switch (opk) {
  37.             <span class="hljs-keyword">case $eq => (<span class="hljs-string">‘=‘ + opv) || <span class="hljs-string">‘IS NULL‘;
  38.             <span class="hljs-keyword">case $ne => (<span class="hljs-string">‘!=‘ + opv) || <span class="hljs-string">‘IS NOT NULL‘;
  39.             <span class="hljs-keyword">case $gt => <span class="hljs-string">‘>‘ + opv;
  40.             <span class="hljs-keyword">case $lt => <span class="hljs-string">‘<‘ + opv;
  41.             <span class="hljs-keyword">case $gte => <span class="hljs-string">‘>=‘ + opv;
  42.             <span class="hljs-keyword">case $lte => <span class="hljs-string">‘<=‘ + opv;
  43.             <span class="hljs-keyword">case $between => <span class="hljs-string">‘BETWEEN ‘ + opv[<span class="hljs-number">0] + <span class="hljs-string">‘ AND ‘ + opv[<span class="hljs-number">1];
  44.             <span class="hljs-keyword">case $notBetween => <span class="hljs-string">‘NOT BETWEEN ‘ + opv[<span class="hljs-number">0] + <span class="hljs-string">‘ AND ‘ + opv[<span class="hljs-number">1];
  45.             <span class="hljs-keyword">case $<span class="hljs-keyword">in => <span class="hljs-string">‘IN (‘ + opv.join(<span class="hljs-string">‘,‘) + <span class="hljs-string">‘)‘;
  46.             <span class="hljs-keyword">case $notIn => <span class="hljs-string">‘NOT IN (‘ + opv.join(<span class="hljs-string">‘,‘) + <span class="hljs-string">‘)‘;
  47.             <span class="hljs-keyword">case $like => <span class="hljs-string">‘LIKE ‘ + opv;
  48.             <span class="hljs-keyword">case $notLike => <span class="hljs-string">‘NOT LIKE ‘ + opv;
  49.         }
  50.     }
  51. }
  52. </span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

我们看一个复杂例子，基本上就是按上述流程来进行转换。

Sequelize：

  1. <code class="javascript"><span class="hljs-keyword">var users = <span class="hljs-keyword">yield User.findAll({
  2.     <span class="hljs-string">‘where‘: {
  3.         <span class="hljs-string">‘id‘: [<span class="hljs-number">3, <span class="hljs-number">4],
  4.         <span class="hljs-string">‘$not‘: [
  5.             {
  6.                 <span class="hljs-string">‘id‘: {
  7.                     <span class="hljs-string">‘$in‘: [<span class="hljs-number">1, <span class="hljs-number">2]
  8.                 }
  9.             },
  10.             {
  11.                 <span class="hljs-string">‘$or‘: [
  12.                     {<span class="hljs-string">‘id‘: [<span class="hljs-number">1, <span class="hljs-number">2]},
  13.                     {<span class="hljs-string">‘nick‘: <span class="hljs-literal">null}
  14.                 ]
  15.             }
  16.         ],
  17.         <span class="hljs-string">‘$and‘: [
  18.             {<span class="hljs-string">‘id‘: [<span class="hljs-number">1, <span class="hljs-number">2]},
  19.             {<span class="hljs-string">‘nick‘: <span class="hljs-literal">null}
  20.         ],
  21.         <span class="hljs-string">‘$or‘: [
  22.             {<span class="hljs-string">‘id‘: [<span class="hljs-number">1, <span class="hljs-number">2]},
  23.             {<span class="hljs-string">‘nick‘: <span class="hljs-literal">null}
  24.         ]
  25.     }
  26. });</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

SQL：

  1. <code class="sql"><span class="hljs-keyword">SELECT <span class="hljs-string">`id`, <span class="hljs-string">`emp_id`, <span class="hljs-string">`nick`, <span class="hljs-string">`department`, <span class="hljs-string">`created_at`, <span class="hljs-string">`updated_at` 
  2. <span class="hljs-keyword">FROM <span class="hljs-string">`users` <span class="hljs-keyword">AS <span class="hljs-string">`user` 
  3. <span class="hljs-keyword">WHERE 
  4.     <span class="hljs-string">`user`.<span class="hljs-string">`id` <span class="hljs-keyword">IN (<span class="hljs-number">3, <span class="hljs-number">4) 
  5. <span class="hljs-keyword">AND 
  6. <span class="hljs-keyword">NOT 
  7. (
  8.     <span class="hljs-string">`user`.<span class="hljs-string">`id` <span class="hljs-keyword">IN (<span class="hljs-number">1, <span class="hljs-number">2) 
  9.     <span class="hljs-keyword">AND 
  10.     (<span class="hljs-string">`user`.<span class="hljs-string">`id` <span class="hljs-keyword">IN (<span class="hljs-number">1, <span class="hljs-number">2) <span class="hljs-keyword">OR <span class="hljs-string">`user`.<span class="hljs-string">`nick` <span class="hljs-keyword">IS <span class="hljs-literal">NULL)
  11. )
  12. <span class="hljs-keyword">AND 
  13. (
  14.     <span class="hljs-string">`user`.<span class="hljs-string">`id` <span class="hljs-keyword">IN (<span class="hljs-number">1, <span class="hljs-number">2) <span class="hljs-keyword">AND <span class="hljs-string">`user`.<span class="hljs-string">`nick` <span class="hljs-keyword">IS <span class="hljs-literal">NULL
  15. ) 
  16. <span class="hljs-keyword">AND 
  17. (
  18.     <span class="hljs-string">`user`.<span class="hljs-string">`id` <span class="hljs-keyword">IN (<span class="hljs-number">1, <span class="hljs-number">2) <span class="hljs-keyword">OR <span class="hljs-string">`user`.<span class="hljs-string">`nick` <span class="hljs-keyword">IS <span class="hljs-literal">NULL
  19. );</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

排序

Sequelize：

  1. <code class="javascript"><span class="hljs-keyword">var users = <span class="hljs-keyword">yield User.findAll({
  2.     <span class="hljs-string">‘order‘: [
  3.         [<span class="hljs-string">‘id‘, <span class="hljs-string">‘DESC‘],
  4.         [<span class="hljs-string">‘nick‘]
  5.     ]
  6. });</span></span></span></span></span></span></code>

SQL：

  1. <code class="sql"><span class="hljs-keyword">SELECT <span class="hljs-string">`id`, <span class="hljs-string">`emp_id`, <span class="hljs-string">`nick`, <span class="hljs-string">`department`, <span class="hljs-string">`created_at`, <span class="hljs-string">`updated_at` 
  2. <span class="hljs-keyword">FROM <span class="hljs-string">`users` <span class="hljs-keyword">AS <span class="hljs-string">`user` 
  3. <span class="hljs-keyword">ORDER <span class="hljs-keyword">BY <span class="hljs-string">`user`.<span class="hljs-string">`id` <span class="hljs-keyword">DESC, <span class="hljs-string">`user`.<span class="hljs-string">`nick`;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

分页

Sequelize：

  1. <code class="javascript"><span class="hljs-keyword">var countPerPage = <span class="hljs-number">20, currentPage = <span class="hljs-number">5;
  2. <span class="hljs-keyword">var users = <span class="hljs-keyword">yield User.findAll({
  3.     <span class="hljs-string">‘limit‘: countPerPage,                      <span class="hljs-comment">// 每页多少条
  4.     <span class="hljs-string">‘offset‘: countPerPage * (currentPage - <span class="hljs-number">1)  <span class="hljs-comment">// 跳过多少条
  5. });</span></span></span></span></span></span></span></span></span></span></code>

SQL：

  1. <code class="sql"><span class="hljs-keyword">SELECT <span class="hljs-string">`id`, <span class="hljs-string">`emp_id`, <span class="hljs-string">`nick`, <span class="hljs-string">`department`, <span class="hljs-string">`created_at`, <span class="hljs-string">`updated_at` 
  2. <span class="hljs-keyword">FROM <span class="hljs-string">`users` <span class="hljs-keyword">AS <span class="hljs-string">`user` 
  3. <span class="hljs-keyword">LIMIT <span class="hljs-number">80, <span class="hljs-number">20;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

其他查询方法

查询一条数据

Sequelize：

  1. <code class="javascript">user = <span class="hljs-keyword">yield User.findById(<span class="hljs-number">1);
  2. user = <span class="hljs-keyword">yield User.findOne({
  3.     <span class="hljs-string">‘where‘: {<span class="hljs-string">‘nick‘: <span class="hljs-string">‘a‘}
  4. });</span></span></span></span></span></span></code>

SQL：

  1. <code class="sql"><span class="hljs-operator"><span class="hljs-keyword">SELECT <span class="hljs-string">`id`, <span class="hljs-string">`emp_id`, <span class="hljs-string">`nick`, <span class="hljs-string">`department`, <span class="hljs-string">`created_at`, <span class="hljs-string">`updated_at` 
  2. <span class="hljs-keyword">FROM <span class="hljs-string">`users` <span class="hljs-keyword">AS <span class="hljs-string">`user` 
  3. <span class="hljs-keyword">WHERE <span class="hljs-string">`user`.<span class="hljs-string">`id` = <span class="hljs-number">1 <span class="hljs-keyword">LIMIT <span class="hljs-number">1;
  4. <span class="hljs-operator"><span class="hljs-keyword">SELECT <span class="hljs-string">`id`, <span class="hljs-string">`emp_id`, <span class="hljs-string">`nick`, <span class="hljs-string">`department`, <span class="hljs-string">`created_at`, <span class="hljs-string">`updated_at` 
  5. <span class="hljs-keyword">FROM <span class="hljs-string">`users` <span class="hljs-keyword">AS <span class="hljs-string">`user` 
  6. <span class="hljs-keyword">WHERE <span class="hljs-string">`user`.<span class="hljs-string">`nick` = <span class="hljs-string">‘a‘ <span class="hljs-keyword">LIMIT <span class="hljs-number">1;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>
查询并获取数量

Sequelize：

  1. <code class="javascript"><span class="hljs-keyword">var result = <span class="hljs-keyword">yield User.findAndCountAll({
  2.     <span class="hljs-string">‘limit‘: <span class="hljs-number">20,
  3.     <span class="hljs-string">‘offset‘: <span class="hljs-number">0
  4. });
  5. <span class="hljs-built_in">console.log(result);</span></span></span></span></span></span></span></code>

SQL：

  1. <code class="sql"><span class="hljs-operator"><span class="hljs-keyword">SELECT <span class="hljs-keyword">count(*) <span class="hljs-keyword">AS <span class="hljs-string">`count` <span class="hljs-keyword">FROM <span class="hljs-string">`users` <span class="hljs-keyword">AS <span class="hljs-string">`user`;
  2. <span class="hljs-operator"><span class="hljs-keyword">SELECT <span class="hljs-string">`id`, <span class="hljs-string">`emp_id`, <span class="hljs-string">`nick`, <span class="hljs-string">`department`, <span class="hljs-string">`created_at`, <span class="hljs-string">`updated_at` 
  3. <span class="hljs-keyword">FROM <span class="hljs-string">`users` <span class="hljs-keyword">AS <span class="hljs-string">`user` 
  4. <span class="hljs-keyword">LIMIT <span class="hljs-number">20;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

这个方法会执行2个SQL，返回的result对象将包含2个字段：result.count是数据总数，result.rows是符合查询条件的所有数据。

批量操作

插入

Sequelize：

  1. <code class="javascript"><span class="hljs-keyword">var users = <span class="hljs-keyword">yield User.bulkCreate(
  2.     [
  3.         {<span class="hljs-string">‘emp_id‘: <span class="hljs-string">‘a‘, <span class="hljs-string">‘nick‘: <span class="hljs-string">‘a‘},
  4.         {<span class="hljs-string">‘emp_id‘: <span class="hljs-string">‘b‘, <span class="hljs-string">‘nick‘: <span class="hljs-string">‘b‘},
  5.         {<span class="hljs-string">‘emp_id‘: <span class="hljs-string">‘c‘, <span class="hljs-string">‘nick‘: <span class="hljs-string">‘c‘}
  6.     ]
  7. );</span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

SQL：

  1. <code class="sql"><span class="hljs-keyword">INSERT <span class="hljs-keyword">INTO <span class="hljs-string">`users` 
  2.     (<span class="hljs-string">`id`,<span class="hljs-string">`emp_id`,<span class="hljs-string">`nick`,<span class="hljs-string">`created_at`,<span class="hljs-string">`updated_at`) 
  3. <span class="hljs-keyword">VALUES 
  4.     (<span class="hljs-literal">NULL,<span class="hljs-string">‘a‘,<span class="hljs-string">‘a‘,<span class="hljs-string">‘2015-11-03 02:43:30‘,<span class="hljs-string">‘2015-11-03 02:43:30‘),
  5.     (<span class="hljs-literal">NULL,<span class="hljs-string">‘b‘,<span class="hljs-string">‘b‘,<span class="hljs-string">‘2015-11-03 02:43:30‘,<span class="hljs-string">‘2015-11-03 02:43:30‘),
  6.     (<span class="hljs-literal">NULL,<span class="hljs-string">‘c‘,<span class="hljs-string">‘c‘,<span class="hljs-string">‘2015-11-03 02:43:30‘,<span class="hljs-string">‘2015-11-03 02:43:30‘);</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

这里需要注意，返回的users数组里面每个对象的id值会是null。如果需要id值，可以重新取下数据。

更新

Sequelize：

  1. <code class="javascript"><span class="hljs-keyword">var affectedRows = <span class="hljs-keyword">yield User.update(
  2.     {<span class="hljs-string">‘nick‘: <span class="hljs-string">‘hhhh‘},
  3.     {
  4.         <span class="hljs-string">‘where‘: {
  5.             <span class="hljs-string">‘id‘: [<span class="hljs-number">2, <span class="hljs-number">3, <span class="hljs-number">4]
  6.         }
  7.     }
  8. );</span></span></span></span></span></span></span></span></span></code>

SQL：

  1. <code class="sql"><span class="hljs-keyword">UPDATE <span class="hljs-string">`users` 
  2. <span class="hljs-keyword">SET <span class="hljs-string">`nick`=<span class="hljs-string">‘hhhh‘,<span class="hljs-string">`updated_at`=<span class="hljs-string">‘2015-11-03 02:51:05‘ 
  3. <span class="hljs-keyword">WHERE <span class="hljs-string">`id` <span class="hljs-keyword">IN (<span class="hljs-number">2, <span class="hljs-number">3, <span class="hljs-number">4);</span></span></span></span></span></span></span></span></span></span></span></span></span></code>

这里返回的affectedRows其实是一个数组，里面只有一个元素，表示更新的数据条数（看起来像是Sequelize的一个bug）。

删除

Sequelize：

  1. <code class="javascript"><span class="hljs-keyword">var affectedRows = <span class="hljs-keyword">yield User.destroy({
  2.     <span class="hljs-string">‘where‘: {<span class="hljs-string">‘id‘: [<span class="hljs-number">2, <span class="hljs-number">3, <span class="hljs-number">4]}
  3. });</span></span></span></span></span></span></span></code>

SQL：

  1. <code class="sql"><span class="hljs-keyword">DELETE <span class="hljs-keyword">FROM <span class="hljs-string">`users` <span class="hljs-keyword">WHERE <span class="hljs-string">`id` <span class="hljs-keyword">IN (<span class="hljs-number">2, <span class="hljs-number">3, <span class="hljs-number">4);</span></span></span></span></span></span></span></span></span></code>

这里返回的affectedRows是一个数字，表示删除的数据条数。

关系

关系一般有三种：一对一、一对多、多对多。Sequelize提供了清晰易用的接口来定义关系、进行表间的操作。

当说到关系查询时，一般会需要获取多张表的数据。有建议用连表查询join的，有不建议的。我的看法是，join查询这种黑科技在数据量小的情况下可以使用，基本没有什么影响，数据量大的时候，join的性能可能会是硬伤，应该尽量避免，可以分别根据索引取单表数据然后在应用层对数据进行join、merge。当然，查询时一定要分页，不要findAll。

一对一

模型定义

Sequelize：

  1. <code class="javascript"><span class="hljs-keyword">var User = sequelize.define(<span class="hljs-string">‘user‘,
  2.     {
  3.         <span class="hljs-string">‘emp_id‘: {
  4.             <span class="hljs-string">‘type‘: Sequelize.CHAR(<span class="hljs-number">10),
  5.             <span class="hljs-string">‘allowNull‘: <span class="hljs-literal">false,
  6.             <span class="hljs-string">‘unique‘: <span class="hljs-literal">true
  7.         }
  8.     }
  9. );
  10. <span class="hljs-keyword">var Account = sequelize.define(<span class="hljs-string">‘account‘,
  11.     {
  12.         <span class="hljs-string">‘email‘: {
  13.             <span class="hljs-string">‘type‘: Sequelize.CHAR(<span class="hljs-number">20),
  14.             <span class="hljs-string">‘allowNull‘: <span class="hljs-literal">false
  15.         }
  16.     }
  17. );
  18. <span class="hljs-comment">/* 
  19.  * User的实例对象将拥有getAccount、setAccount、addAccount方法
  20.  */
  21. User.hasOne(Account);
  22. <span class="hljs-comment">/*
  23.  * Account的实例对象将拥有getUser、setUser、addUser方法
  24.  */
  25. Account.belongsTo(User);</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

SQL：

  1. <code class="sql"><span class="hljs-operator"><span class="hljs-keyword">CREATE <span class="hljs-keyword">TABLE <span class="hljs-keyword">IF <span class="hljs-keyword">NOT <span class="hljs-keyword">EXISTS <span class="hljs-string">`users` (
  2.     <span class="hljs-string">`id` <span class="hljs-built_in">INTEGER <span class="hljs-keyword">NOT <span class="hljs-literal">NULL auto_increment , 
  3.     <span class="hljs-string">`emp_id` <span class="hljs-built_in">CHAR(<span class="hljs-number">10) <span class="hljs-keyword">NOT <span class="hljs-literal">NULL <span class="hljs-keyword">UNIQUE, 
  4.     <span class="hljs-string">`created_at` DATETIME <span class="hljs-keyword">NOT <span class="hljs-literal">NULL, 
  5.     <span class="hljs-string">`updated_at` DATETIME <span class="hljs-keyword">NOT <span class="hljs-literal">NULL, 
  6.     <span class="hljs-keyword">PRIMARY <span class="hljs-keyword">KEY (<span class="hljs-string">`id`)
  7. ) <span class="hljs-keyword">ENGINE=<span class="hljs-keyword">InnoDB;
  8. <span class="hljs-operator"><span class="hljs-keyword">CREATE <span class="hljs-keyword">TABLE <span class="hljs-keyword">IF <span class="hljs-keyword">NOT <span class="hljs-keyword">EXISTS <span class="hljs-string">`accounts` (
  9.     <span class="hljs-string">`id` <span class="hljs-built_in">INTEGER <span class="hljs-keyword">NOT <span class="hljs-literal">NULL auto_increment , 
  10.     <span class="hljs-string">`email` <span class="hljs-built_in">CHAR(<span class="hljs-number">20) <span class="hljs-keyword">NOT <span class="hljs-literal">NULL, 
  11.     <span class="hljs-string">`created_at` DATETIME <span class="hljs-keyword">NOT <span class="hljs-literal">NULL, 
  12.     <span class="hljs-string">`updated_at` DATETIME <span class="hljs-keyword">NOT <span class="hljs-literal">NULL, 
  13.     <span class="hljs-string">`user_id` <span class="hljs-built_in">INTEGER, 
  14.     <span class="hljs-keyword">PRIMARY <span class="hljs-keyword">KEY (<span class="hljs-string">`id`), 
  15.     <span class="hljs-keyword">FOREIGN <span class="hljs-keyword">KEY (<span class="hljs-string">`user_id`) <span class="hljs-keyword">REFERENCES <span class="hljs-string">`users` (<span class="hljs-string">`id`) <span class="hljs-keyword">ON <span class="hljs-keyword">DELETE <span class="hljs-keyword">SET <span class="hljs-literal">NULL <span class="hljs-keyword">ON <span class="hljs-keyword">UPDATE <span class="hljs-keyword">CASCADE
  16. ) <span class="hljs-keyword">ENGINE=<span class="hljs-keyword">InnoDB;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

可以看到，这种关系中外键user_id加在了Account上。另外，Sequelize还给我们生成了外键约束。

一般来说，外键约束在有些自己定制的数据库系统里面是禁止的，因为会带来一些性能问题。所以，建表的SQL一般就去掉约束，同时给外键加一个索引（加速查询），数据的一致性就靠应用层来保证了。

关系操作

增

Sequelize：

  1. <code class="javascript"><span class="hljs-keyword">var user = <span class="hljs-keyword">yield User.create({<span class="hljs-string">‘emp_id‘: <span class="hljs-string">‘1‘});
  2. <span class="hljs-keyword">var account = user.createAccount({<span class="hljs-string">‘email‘: <span class="hljs-string">‘a‘});
  3. <span class="hljs-built_in">console.log(account.get({<span class="hljs-string">‘plain‘: <span class="hljs-literal">true}));</span></span></span></span></span></span></span></span></span></span></code>

SQL：

  1. <code class="sql"><span class="hljs-operator"><span class="hljs-keyword">INSERT <span class="hljs-keyword">INTO <span class="hljs-string">`users` 
  2. (<span class="hljs-string">`id`,<span class="hljs-string">`emp_id`,<span class="hljs-string">`updated_at`,<span class="hljs-string">`created_at`) 
  3. <span class="hljs-keyword">VALUES 
  4. (<span class="hljs-keyword">DEFAULT,<span class="hljs-string">‘1‘,<span class="hljs-string">‘2015-11-03 06:24:53‘,<span class="hljs-string">‘2015-11-03 06:24:53‘);
  5. <span class="hljs-operator"><span class="hljs-keyword">INSERT <span class="hljs-keyword">INTO <span class="hljs-string">`accounts` 
  6. (<span class="hljs-string">`id`,<span class="hljs-string">`email`,<span class="hljs-string">`user_id`,<span class="hljs-string">`updated_at`,<span class="hljs-string">`created_at`) 
  7. <span class="hljs-keyword">VALUES 
  8. (<span class="hljs-keyword">DEFAULT,<span class="hljs-string">‘a‘,<span class="hljs-number">1,<span class="hljs-string">‘2015-11-03 06:24:53‘,<span class="hljs-string">‘2015-11-03 06:24:53‘);</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

SQL执行逻辑是：

  • 使用对应的的user_id作为外键在accounts表里插入一条数据。

改

Sequelize：

  1. <code class="javascript"><span class="hljs-keyword">var anotherAccount = <span class="hljs-keyword">yield Account.create({<span class="hljs-string">‘email‘: <span class="hljs-string">‘b‘});
  2. <span class="hljs-built_in">console.log(anotherAccount);
  3. anotherAccount = <span class="hljs-keyword">yield user.setAccount(anotherAccount);
  4. <span class="hljs-built_in">console.log(anotherAccount);</span></span></span></span></span></span></span></code>

SQL：

  1. <code class="sql"><span class="hljs-operator"><span class="hljs-keyword">INSERT <span class="hljs-keyword">INTO <span class="hljs-string">`accounts` 
  2. (<span class="hljs-string">`id`,<span class="hljs-string">`email`,<span class="hljs-string">`updated_at`,<span class="hljs-string">`created_at`) 
  3. <span class="hljs-keyword">VALUES 
  4. (<span class="hljs-keyword">DEFAULT,<span class="hljs-string">‘b‘,<span class="hljs-string">‘2015-11-03 06:37:14‘,<span class="hljs-string">‘2015-11-03 06:37:14‘);
  5. <span class="hljs-operator"><span class="hljs-keyword">SELECT <span class="hljs-string">`id`, <span class="hljs-string">`email`, <span class="hljs-string">`created_at`, <span class="hljs-string">`updated_at`, <span class="hljs-string">`user_id` 
  6. <span class="hljs-keyword">FROM <span class="hljs-string">`accounts` <span class="hljs-keyword">AS <span class="hljs-string">`account` <span class="hljs-keyword">WHERE (<span class="hljs-string">`account`.<span class="hljs-string">`user_id` = <span class="hljs-number">1);
  7. <span class="hljs-operator"><span class="hljs-keyword">UPDATE <span class="hljs-string">`accounts` <span class="hljs-keyword">SET <span class="hljs-string">`user_id`=<span class="hljs-literal">NULL,<span class="hljs-string">`updated_at`=<span class="hljs-string">‘2015-11-03 06:37:14‘ <span class="hljs-keyword">WHERE <span class="hljs-string">`id` = <span class="hljs-number">1;
  8. <span class="hljs-operator"><span class="hljs-keyword">UPDATE <span class="hljs-string">`accounts` <span class="hljs-keyword">SET <span class="hljs-string">`user_id`=<span class="hljs-number">1,<span class="hljs-string">`updated_at`=<span class="hljs-string">‘2015-11-03 06:37:14‘ <span class="hljs-keyword">WHERE <span class="hljs-string">`id` = <span class="hljs-number">2;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

SQL执行逻辑是：

  • 插入一条account数据，此时外键user_id是空的，还没有关联user

  • 找出当前user所关联的account并将其user_id置为`NUL（为了保证一对一关系）

  • 设置新的acount的外键user_id为user的属性id，生成关系

删

Sequelize：

  1. <code class="javascript"><span class="hljs-keyword">yield user.setAccount(<span class="hljs-literal">null);</span></span></code>

SQL：

  1. <code class="sql"><span class="hljs-operator"><span class="hljs-keyword">SELECT <span class="hljs-string">`id`, <span class="hljs-string">`email`, <span class="hljs-string">`created_at`, <span class="hljs-string">`updated_at`, <span class="hljs-string">`user_id` 
  2. <span class="hljs-keyword">FROM <span class="hljs-string">`accounts` <span class="hljs-keyword">AS <span class="hljs-string">`account` 
  3. <span class="hljs-keyword">WHERE (<span class="hljs-string">`account`.<span class="hljs-string">`user_id` = <span class="hljs-number">1);
  4. <span class="hljs-operator"><span class="hljs-keyword">UPDATE <span class="hljs-string">`accounts` 
  5. <span class="hljs-keyword">SET <span class="hljs-string">`user_id`=<span class="hljs-literal">NULL,<span class="hljs-string">`updated_at`=<span class="hljs-string">‘2015-11-04 00:11:35‘ 
  6. <span class="hljs-keyword">WHERE <span class="hljs-string">`id` = <span class="hljs-number">1;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

这里的删除实际上只是“切断”关系，并不会真正的物理删除记录。

SQL执行逻辑是：

  • 找出user所关联的account数据

  • 将其外键user_id设置为NULL，完成关系的“切断”

查

Sequelize：

  1. <code class="javascript"><span class="hljs-keyword">var account = <span class="hljs-keyword">yield user.getAccount();
  2. <span class="hljs-built_in">console.log(account);</span></span></span></code>

SQL：

  1. <code class="sql"><span class="hljs-keyword">SELECT <span class="hljs-string">`id`, <span class="hljs-string">`email`, <span class="hljs-string">`created_at`, <span class="hljs-string">`updated_at`, <span class="hljs-string">`user_id` 
  2. <span class="hljs-keyword">FROM <span class="hljs-string">`accounts` <span class="hljs-keyword">AS <span class="hljs-string">`account` 
  3. <span class="hljs-keyword">WHERE (<span class="hljs-string">`account`.<span class="hljs-string">`user_id` = <span class="hljs-number">1);</span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

这里就是调用user的getAccount方法，根据外键来获取对应的account。

但是其实我们用面向对象的思维来思考应该是获取user的时候就能通过user.account的方式来访问account对象。这可以通过Sequelize的eager loading（急加载，和懒加载相反）来实现。

eager loading的含义是说，取一个模型的时候，同时也把相关的模型数据也给我取过来（我很着急，不能按默认那种取一个模型就取一个模型的方式，我还要更多）。方法如下：

Sequelize：

  1. <code class="javascript"><span class="hljs-keyword">var user = <span class="hljs-keyword">yield User.findById(<span class="hljs-number">1, {
  2.     <span class="hljs-string">‘include‘: [Account]
  3. });
  4. <span class="hljs-built_in">console.log(user.get({<span class="hljs-string">‘plain‘: <span class="hljs-literal">true}));
  5. <span class="hljs-comment">/* 
  6.  * 输出类似：
  7.  { id: 1,
  8.   emp_id: ‘1‘,
  9.   created_at: Tue Nov 03 2015 15:25:27 GMT+0800 (CST),
  10.   updated_at: Tue Nov 03 2015 15:25:27 GMT+0800 (CST),
  11.   account:
  12.    { id: 2,
  13.      email: ‘b‘,
  14.      created_at: Tue Nov 03 2015 15:25:27 GMT+0800 (CST),
  15.      updated_at: Tue Nov 03 2015 15:25:27 GMT+0800 (CST),
  16.      user_id: 1 } }
  17.  */</span></span></span></span></span></span></span></span></code>

SQL：

  1. <code class="sql"><span class="hljs-keyword">SELECT <span class="hljs-string">`user`.<span class="hljs-string">`id`, <span class="hljs-string">`user`.<span class="hljs-string">`emp_id`, <span class="hljs-string">`user`.<span class="hljs-string">`created_at`, <span class="hljs-string">`user`.<span class="hljs-string">`updated_at`, <span cl="" <="" div="">
  2.                   
  3.  
  4.                     <div class="">
  5.                         <ul class="m-news-opt fix">
  6.                             <li class="opt-item">
  7.                                 <a href="/sql_question-428617.html" target="_blank"><p>< 上一篇</p><p class="ellipsis">处理本地数据库和云数据库存储的注意事项</p></a>
  8.                             </li>
  9.                             <li class="opt-item ta-r">
  10.                                  <a href="/sql_question-428619.html" target="_blank"><p>下一篇 ></p><p class="ellipsis">db2导出数据并导入hive临时表中</p></a>
  11.                             </li>
  12.                         </ul>
  13.                     </div>
  14.                     
  15.                     
  16.                     
  17.                     
  18.                 </span></span></span></span></span></span></span></span></span></span></code>

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！