Go orm框架gorm学习 - Powered by Discuz! Archiver

首页 › Go语言论坛 › Go orm框架gorm学习

太子殿下煮鸡汤 發表於 2019-6-5 14:01:00

Go orm框架gorm学习

<svg xmlns="http://www.w3.org/2000/svg" style="display: none">
         <path stroke-linecap="round" d="M5,0 0,2.5 5,5z" id="raphael-marker-block" style="-webkit-tap-highlight-color: rgba(0, 0, 0, 0)"></path>
         </svg>
         <p>之前咱们学习过原生的Go连接MYSQL的方法，使用Go自带的<code>"database/sql"</code>数据库连接api，<code>"github.com/go-sql-driver/mysql"</code>MYSQL驱动，通过比较原生的写法去写sql和处理事务。目前开源界也有很多封装好的orm操作框架，帮我们简省一些重复的操作，提高代码可读性。<code>gorm</code>就是这样的一款作品，我们来学习一下gorm的操作流程。</p>
<h4>安装</h4>
<pre><code>go get -u github.com/jinzhu/gorm
</code></pre>
<h4>数据库连接</h4>
<p>要连接到数据库首先要导入驱动程序。例如</p>
<pre><code>import _ "github.com/go-sql-driver/mysql"
</code></pre>
<p>为了方便记住导入路径，GORM包装了一些驱动。</p>
<pre><code>import _ "github.com/jinzhu/gorm/dialects/mysql"
// import _ "github.com/jinzhu/gorm/dialects/postgres"
// import _ "github.com/jinzhu/gorm/dialects/sqlite"
// import _ "github.com/jinzhu/gorm/dialects/mssql"
</code></pre>
<p>所以包名可以改为如上：</p>
<pre><code class="prism language-go"><span class="token keyword">import</span> <span class="token punctuation">(</span>
<span class="token string">"github.com/jinzhu/gorm"</span>
<span class="token boolean">_</span> <span class="token string">"github.com/jinzhu/gorm/dialects/mysql"</span>
<span class="token punctuation">)</span>

<span class="token keyword">func</span> <span class="token function">main</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
db<span class="token punctuation">,</span> err <span class="token operator">:=</span> gorm<span class="token punctuation">.</span><span class="token function">Open</span><span class="token punctuation">(</span><span class="token string">"mysql"</span><span class="token punctuation">,</span> <span class="token string">"user:password@tcp(IP:port)/dbname?charset=utf8&parseTime=True&loc=Local"</span><span class="token punctuation">)</span>
db<span class="token punctuation">.</span><span class="token function">DB</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">SetMaxIdleConns</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span>
db<span class="token punctuation">.</span><span class="token function">DB</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">SetMaxOpenConns</span><span class="token punctuation">(</span><span class="token number">100</span><span class="token punctuation">)</span>
<span class="token keyword">defer</span> db<span class="token punctuation">.</span><span class="token function">Close</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token punctuation">}</span>
</code></pre>
<p><strong>注：为了处理<code>time.Time</code>，你需要包括<code>parseTime</code>作为参数。</strong></p>
<h4>数据模型定义</h4>
<h5>表名，列名如何对应结构体</h5>
<p>在Gorm中，表名是结构体名的复数形式，列名是字段名的蛇形小写。</p>
<p><strong>即，如果有一个user表，那么如果你定义的结构体名为：User，gorm会默认表名为users而不是user。</strong></p>
<p>例如有如下表结构定义：</p>
<pre><code class="prism language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token punctuation">`</span>areas<span class="token punctuation">`</span> <span class="token punctuation">(</span>
<span class="token punctuation">`</span>id<span class="token punctuation">`</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">COMMENT</span> <span class="token string">'主键id'</span><span class="token punctuation">,</span>
<span class="token punctuation">`</span>area_id<span class="token punctuation">`</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">COMMENT</span> <span class="token string">'区县id'</span><span class="token punctuation">,</span>
<span class="token punctuation">`</span>area_name<span class="token punctuation">`</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">45</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">COMMENT</span> <span class="token string">'区县名'</span><span class="token punctuation">,</span>
<span class="token punctuation">`</span>city_id<span class="token punctuation">`</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">COMMENT</span> <span class="token string">'城市id'</span><span class="token punctuation">,</span>
<span class="token punctuation">`</span>city_name<span class="token punctuation">`</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">45</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">COMMENT</span> <span class="token string">'城市名称'</span><span class="token punctuation">,</span>
<span class="token punctuation">`</span>province_id<span class="token punctuation">`</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">COMMENT</span> <span class="token string">'省份id'</span><span class="token punctuation">,</span>
<span class="token punctuation">`</span>province_name<span class="token punctuation">`</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">45</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">COMMENT</span> <span class="token string">'省份名称'</span><span class="token punctuation">,</span>
<span class="token punctuation">`</span>area_status<span class="token punctuation">`</span> <span class="token keyword">tinyint</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">'1'</span> <span class="token keyword">COMMENT</span> <span class="token string">'该条区域信息是否可用： 1:可用2：不可用'</span><span class="token punctuation">,</span>
<span class="token punctuation">`</span>created_at<span class="token punctuation">`</span> <span class="token keyword">timestamp</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">COMMENT</span> <span class="token string">'创建时间'</span><span class="token punctuation">,</span>
<span class="token punctuation">`</span>updated_at<span class="token punctuation">`</span> <span class="token keyword">timestamp</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">COMMENT</span> <span class="token string">'更新时间'</span><span class="token punctuation">,</span>
<span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">InnoDB</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token operator">=</span><span class="token number">1</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8mb4 <span class="token keyword">COMMENT</span><span class="token operator">=</span><span class="token string">'区域表'</span>
</code></pre>
<p>那么对应的结构体定义如下：</p>
<pre><code class="prism language-go"><span class="token keyword">type</span> Area <span class="token keyword">struct</span> <span class="token punctuation">{</span>
Id <span class="token builtin">int</span>
AreaId <span class="token builtin">int</span>
AreaName <span class="token builtin">string</span>
CityId <span class="token builtin">int</span>
CityName <span class="token builtin">string</span>
ProvinceId <span class="token builtin">int</span>
ProvinceName <span class="token builtin">string</span>
AreaStatus <span class="token builtin">int</span>
CreatedAt time<span class="token punctuation">.</span>Time
UpdatedAt time<span class="token punctuation">.</span>Time
<span class="token punctuation">}</span>
</code></pre>
<p>如何全局禁用表名复数呢？</p>
<p>可以在创建数据库连接的时候设置如下参数：</p>
<pre><code class="prism language-go"><span class="token comment">// 全局禁用表名复数</span>
db<span class="token punctuation">.</span><span class="token function">SingularTable</span><span class="token punctuation">(</span><span class="token boolean">true</span><span class="token punctuation">)</span> <span class="token comment">// 如果设置为true,`User`的默认表名为`user`,使用`TableName`设置的表名不受影响</span>
</code></pre>
<p>这样的话，表名默认即为结构体的首字母小写形式。</p>
<h4>CRUD 使用</h4>
<p>下面我们使用一张User表来就CRUD做一些操作示例：</p>
<p>表结构如下：</p>
<pre><code class="prism language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token punctuation">`</span><span class="token keyword">user</span><span class="token punctuation">`</span> <span class="token punctuation">(</span>
<span class="token punctuation">`</span>id<span class="token punctuation">`</span> <span class="token keyword">bigint</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
<span class="token punctuation">`</span>name<span class="token punctuation">`</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">''</span><span class="token punctuation">,</span>
<span class="token punctuation">`</span>age<span class="token punctuation">`</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">'0'</span><span class="token punctuation">,</span>
<span class="token punctuation">`</span>sex<span class="token punctuation">`</span> <span class="token keyword">tinyint</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">'0'</span><span class="token punctuation">,</span>
<span class="token punctuation">`</span>phone<span class="token punctuation">`</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">40</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token string">''</span><span class="token punctuation">,</span>
<span class="token punctuation">`</span>create_at<span class="token punctuation">`</span> <span class="token keyword">timestamp</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span><span class="token punctuation">,</span>
<span class="token punctuation">`</span>updated_at<span class="token punctuation">`</span> <span class="token keyword">timestamp</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span><span class="token punctuation">,</span>
<span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>id<span class="token punctuation">`</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">InnoDB</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token operator">=</span><span class="token number">8</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8mb4
</code></pre>
<p>首先初始化数据库连接：</p>
<pre><code class="prism language-go"><span class="token keyword">package</span> main

<span class="token keyword">import</span> <span class="token punctuation">(</span>
<span class="token string">"github.com/jinzhu/gorm"</span>
<span class="token boolean">_</span> <span class="token string">"github.com/jinzhu/gorm/dialects/mysql"</span>
<span class="token punctuation">)</span>

<span class="token keyword">var</span> db <span class="token operator">*</span>gorm<span class="token punctuation">.</span>DB

<span class="token keyword">type</span> User <span class="token keyword">struct</span> <span class="token punctuation">{</span>
Id <span class="token builtin">int</span>
Name <span class="token builtin">string</span>
Age <span class="token builtin">int</span>
Sex <span class="token builtin">byte</span>
Phone <span class="token builtin">string</span>
<span class="token punctuation">}</span>

<span class="token keyword">func</span> <span class="token function">init</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
<span class="token keyword">var</span> err <span class="token builtin">error</span>
db<span class="token punctuation">,</span> err <span class="token operator">=</span> gorm<span class="token punctuation">.</span><span class="token function">Open</span><span class="token punctuation">(</span><span class="token string">"mysql"</span><span class="token punctuation">,</span> <span class="token string">"root:123456@tcp(127.0.0.1:3306)/test?charset=utf8&parseTime=True&loc=Local"</span><span class="token punctuation">)</span>
<span class="token keyword">if</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
<span class="token function">panic</span><span class="token punctuation">(</span>err<span class="token punctuation">)</span>
<span class="token punctuation">}</span>
<span class="token comment">//设置全局表名禁用复数</span>
db<span class="token punctuation">.</span><span class="token function">SingularTable</span><span class="token punctuation">(</span><span class="token boolean">true</span><span class="token punctuation">)</span>
<span class="token punctuation">}</span>

</code></pre>
<p>下面所有的操作都是在上面的初始化连接上执行的操作。</p>
<h5>插入</h5>
<pre><code class="prism language-go"><span class="token comment">//插入数据</span>
<span class="token keyword">func</span> <span class="token punctuation">(</span>user <span class="token operator">*</span>User<span class="token punctuation">)</span> <span class="token function">Insert</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">{</span>
<span class="token comment">//这里使用了Table()函数，如果你没有指定全局表名禁用复数，或者是表名跟结构体名不一样的时候</span>
<span class="token comment">//你可以自己在sql中指定表名。这里是示例，本例中这个函数可以去除。</span>
db<span class="token punctuation">.</span><span class="token function">Table</span><span class="token punctuation">(</span><span class="token string">"user"</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Create</span><span class="token punctuation">(</span>user<span class="token punctuation">)</span>
<span class="token punctuation">}</span>
</code></pre>
<h5>更新</h5>
<pre><code class="prism language-go"><span class="token comment">//注意，Model方法必须要和Update方法一起使用</span>
<span class="token comment">//使用效果相当于Model中设置更新的主键key（如果没有where指定，那么默认更新的key为id），Update中设置更新的值</span>
<span class="token comment">//如果Model中没有指定id值，且也没有指定where条件，那么将更新全表</span>
<span class="token comment">//相当于：update user set name='xiaoming' where id=1;</span>
user <span class="token operator">:=</span> User<span class="token punctuation">{</span>Id<span class="token punctuation">:</span> <span class="token number">1</span><span class="token punctuation">,</span>Name<span class="token punctuation">:</span><span class="token string">"xiaoming"</span><span class="token punctuation">}</span>
db<span class="token punctuation">.</span><span class="token function">Model</span><span class="token punctuation">(</span><span class="token operator">&</span>user<span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Update</span><span class="token punctuation">(</span>user<span class="token punctuation">)</span>

<span class="token comment">//注意到上面Update中使用了一个Struct，你也可以使用map对象。</span>
<span class="token comment">//需要注意的是：使用Struct的时候，只会更新Struct中这些非空的字段。</span>
<span class="token comment">//对于string类型字段的""，int类型字段0，bool类型字段的false都被认为是空白值，不会去更新表</span>

<span class="token comment">//下面这个更新操作只使用了where条件没有在Model中指定id</span>
<span class="token comment">//update user set name='xiaohong' wehre sex=1</span>
db<span class="token punctuation">.</span><span class="token function">Model</span><span class="token punctuation">(</span><span class="token operator">&</span>User<span class="token punctuation">{</span><span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Where</span><span class="token punctuation">(</span><span class="token string">"sex = ?"</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Update</span><span class="token punctuation">(</span><span class="token string">"name"</span><span class="token punctuation">,</span><span class="token string">"xiaohong"</span><span class="token punctuation">)</span>
</code></pre>
<p>如果你想手动将某个字段set为空值, 可以使用单独选定某些字段的方式来更新：</p>
<pre><code class="prism language-go">user <span class="token operator">:=</span> User<span class="token punctuation">{</span>Id<span class="token punctuation">:</span> <span class="token number">1</span><span class="token punctuation">}</span>
db<span class="token punctuation">.</span><span class="token function">Model</span><span class="token punctuation">(</span><span class="token operator">&</span>user<span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Select</span><span class="token punctuation">(</span><span class="token string">"name"</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Update</span><span class="token punctuation">(</span><span class="token keyword">map</span><span class="token punctuation">[</span><span class="token builtin">string</span><span class="token punctuation">]</span><span class="token keyword">interface</span><span class="token punctuation">{</span><span class="token punctuation">}</span><span class="token punctuation">{</span><span class="token string">"name"</span><span class="token punctuation">:</span><span class="token string">""</span><span class="token punctuation">,</span><span class="token string">"age"</span><span class="token punctuation">:</span><span class="token number">0</span><span class="token punctuation">}</span><span class="token punctuation">)</span>
</code></pre>
<p>忽略掉某些字段：</p>
<p>当你的更新的参数为结构体，而结构体中某些字段你又不想去更新，那么可以使用Omit方法过滤掉这些不想update到库的字段：</p>
<pre><code class="prism language-go">user <span class="token operator">:=</span> User<span class="token punctuation">{</span>Id<span class="token punctuation">:</span> <span class="token number">1</span><span class="token punctuation">,</span>Name<span class="token punctuation">:</span><span class="token string">"xioaming"</span><span class="token punctuation">,</span>Age<span class="token punctuation">:</span><span class="token number">12</span><span class="token punctuation">}</span>
db<span class="token punctuation">.</span><span class="token function">Model</span><span class="token punctuation">(</span><span class="token operator">&</span>user<span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Omit</span><span class="token punctuation">(</span><span class="token string">"name"</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Update</span><span class="token punctuation">(</span><span class="token operator">&</span>user<span class="token punctuation">)</span>
</code></pre>
<h5>删除</h5>
<pre><code class="prism language-go"><span class="token comment">//delete from user where id=1;</span>
user <span class="token operator">:=</span> User<span class="token punctuation">{</span>Id<span class="token punctuation">:</span> <span class="token number">1</span><span class="token punctuation">}</span>
db<span class="token punctuation">.</span><span class="token function">Delete</span><span class="token punctuation">(</span><span class="token operator">&</span>user<span class="token punctuation">)</span>

<span class="token comment">//delete from user where id > 11;</span>
db<span class="token punctuation">.</span><span class="token function">Delete</span><span class="token punctuation">(</span><span class="token operator">&</span>User<span class="token punctuation">{</span><span class="token punctuation">}</span><span class="token punctuation">,</span><span class="token string">"id > ?"</span><span class="token punctuation">,</span><span class="token number">11</span><span class="token punctuation">)</span>

</code></pre>
<h5>事务</h5>
<pre><code class="prism language-go"><span class="token keyword">func</span> <span class="token function">CreateAnimals</span><span class="token punctuation">(</span>db <span class="token operator">*</span>gorm<span class="token punctuation">.</span>DB<span class="token punctuation">)</span> err <span class="token punctuation">{</span>
tx <span class="token operator">:=</span> db<span class="token punctuation">.</span><span class="token function">Begin</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token comment">// 注意，一旦你在一个事务中，使用tx作为数据库句柄</span>

<span class="token keyword">if</span> err <span class="token operator">:=</span> tx<span class="token punctuation">.</span><span class="token function">Create</span><span class="token punctuation">(</span><span class="token operator">&</span>Animal<span class="token punctuation">{</span>Name<span class="token punctuation">:</span> <span class="token string">"Giraffe"</span><span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">.</span>Error<span class="token punctuation">;</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
tx<span class="token punctuation">.</span><span class="token function">Rollback</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token keyword">return</span> err
<span class="token punctuation">}</span>

<span class="token keyword">if</span> err <span class="token operator">:=</span> tx<span class="token punctuation">.</span><span class="token function">Create</span><span class="token punctuation">(</span><span class="token operator">&</span>Animal<span class="token punctuation">{</span>Name<span class="token punctuation">:</span> <span class="token string">"Lion"</span><span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">.</span>Error<span class="token punctuation">;</span> err <span class="token operator">!=</span> <span class="token boolean">nil</span> <span class="token punctuation">{</span>
tx<span class="token punctuation">.</span><span class="token function">Rollback</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token keyword">return</span> err
<span class="token punctuation">}</span>

tx<span class="token punctuation">.</span><span class="token function">Commit</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token keyword">return</span> <span class="token boolean">nil</span>
<span class="token punctuation">}</span>
</code></pre>
<h5>查询：</h5>
<pre><code class="prism language-go"><span class="token keyword">func</span> <span class="token punctuation">(</span>user <span class="token operator">*</span>User<span class="token punctuation">)</span> <span class="token function">query</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">(</span>u <span class="token punctuation">[</span><span class="token punctuation">]</span>User<span class="token punctuation">)</span> <span class="token punctuation">{</span>
<span class="token comment">//查询所有记录</span>
db<span class="token punctuation">.</span><span class="token function">Find</span><span class="token punctuation">(</span><span class="token operator">&</span>u<span class="token punctuation">)</span>

<span class="token comment">//Find方法可以带 where 参数</span>
db<span class="token punctuation">.</span><span class="token function">Find</span><span class="token punctuation">(</span><span class="token operator">&</span>u<span class="token punctuation">,</span><span class="token string">"id > ? and age > ?"</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">12</span><span class="token punctuation">)</span>

<span class="token comment">//带where 子句的查询，注意where要在find前面</span>
db<span class="token punctuation">.</span><span class="token function">Where</span><span class="token punctuation">(</span><span class="token string">"id > ?"</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Find</span><span class="token punctuation">(</span><span class="token operator">&</span>u<span class="token punctuation">)</span>

<span class="token comment">// where name in ("xiaoming","xiaohong")</span>
db<span class="token punctuation">.</span><span class="token function">Where</span><span class="token punctuation">(</span><span class="token string">"name in (?)"</span><span class="token punctuation">,</span><span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token builtin">string</span><span class="token punctuation">{</span><span class="token string">"xiaoming"</span><span class="token punctuation">,</span><span class="token string">"xiaohong"</span><span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Find</span><span class="token punctuation">(</span><span class="token operator">&</span>u<span class="token punctuation">)</span>

<span class="token comment">//获取第一条记录，按照主键顺序排序</span>
db<span class="token punctuation">.</span><span class="token function">First</span><span class="token punctuation">(</span><span class="token operator">&</span>u<span class="token punctuation">)</span>

<span class="token comment">//First方法可以带where 条件</span>
db<span class="token punctuation">.</span><span class="token function">First</span><span class="token punctuation">(</span><span class="token operator">&</span>u<span class="token punctuation">,</span><span class="token string">"where sex = ?"</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span>

<span class="token comment">//获取最后一条记录，按照主键顺序排序</span>
<span class="token comment">//同样 last方法也可以带where条件</span>
db<span class="token punctuation">.</span><span class="token function">Last</span><span class="token punctuation">(</span><span class="token operator">&</span>u<span class="token punctuation">)</span>

<span class="token keyword">return</span> u
<span class="token punctuation">}</span>
</code></pre>
<p>注意：方法中带的<code>&u</code>表示是返回值用u这个对象来接收。</p>
<p>上面的查询都将返回表中所有的字段，如果你想指定查询某些字段该怎么做呢？</p>
<h5>指定查询字段-Select</h5>
<pre><code class="prism language-go"><span class="token comment">//指定查询字段</span>
db<span class="token punctuation">.</span><span class="token function">Select</span><span class="token punctuation">(</span><span class="token string">"name,age"</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Where</span><span class="token punctuation">(</span><span class="token keyword">map</span><span class="token punctuation">[</span><span class="token builtin">string</span><span class="token punctuation">]</span><span class="token keyword">interface</span><span class="token punctuation">{</span><span class="token punctuation">}</span><span class="token punctuation">{</span><span class="token string">"age"</span><span class="token punctuation">:</span><span class="token number">12</span><span class="token punctuation">,</span><span class="token string">"sex"</span><span class="token punctuation">:</span><span class="token number">1</span><span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Find</span><span class="token punctuation">(</span><span class="token operator">&</span>u<span class="token punctuation">)</span>
</code></pre>
<h5>使用Struct和map作为查询条件</h5>
<pre><code class="prism language-go"><span class="token comment">//使用Struct，相当于：select * from user where age =12 and sex=1</span>
db<span class="token punctuation">.</span><span class="token function">Where</span><span class="token punctuation">(</span><span class="token operator">&</span>User<span class="token punctuation">{</span>Age<span class="token punctuation">:</span><span class="token number">12</span><span class="token punctuation">,</span>Sex<span class="token punctuation">:</span><span class="token number">1</span><span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Find</span><span class="token punctuation">(</span><span class="token operator">&</span>u<span class="token punctuation">)</span>

<span class="token comment">//等同上一句</span>
db<span class="token punctuation">.</span><span class="token function">Where</span><span class="token punctuation">(</span><span class="token keyword">map</span><span class="token punctuation">[</span><span class="token builtin">string</span><span class="token punctuation">]</span><span class="token keyword">interface</span><span class="token punctuation">{</span><span class="token punctuation">}</span><span class="token punctuation">{</span><span class="token string">"age"</span><span class="token punctuation">:</span><span class="token number">12</span><span class="token punctuation">,</span><span class="token string">"sex"</span><span class="token punctuation">:</span><span class="token number">1</span><span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Find</span><span class="token punctuation">(</span><span class="token operator">&</span>u<span class="token punctuation">)</span>
</code></pre>
<h5>not 条件的使用</h5>
<pre><code class="prism language-go"><span class="token comment">//where name not in ("xiaoming","xiaohong")</span>
db<span class="token punctuation">.</span><span class="token function">Not</span><span class="token punctuation">(</span><span class="token string">"name"</span><span class="token punctuation">,</span><span class="token string">"xiaoming"</span><span class="token punctuation">,</span><span class="token string">"xiaohong"</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Find</span><span class="token punctuation">(</span><span class="token operator">&</span>u<span class="token punctuation">)</span>

<span class="token comment">//同上</span>
db<span class="token punctuation">.</span><span class="token function">Not</span><span class="token punctuation">(</span><span class="token string">"name"</span><span class="token punctuation">,</span><span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token builtin">string</span><span class="token punctuation">{</span><span class="token string">"xiaoming"</span><span class="token punctuation">,</span><span class="token string">"xiaohong"</span><span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Find</span><span class="token punctuation">(</span><span class="token operator">&</span>u<span class="token punctuation">)</span>
</code></pre>
<h5>or 的使用</h5>
<pre><code class="prism language-go"><span class="token comment">//where age > 12 or sex = 1</span>
db<span class="token punctuation">.</span><span class="token function">Where</span><span class="token punctuation">(</span><span class="token string">"age > ?"</span><span class="token punctuation">,</span><span class="token number">12</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Or</span><span class="token punctuation">(</span><span class="token string">"sex = ?"</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Find</span><span class="token punctuation">(</span><span class="token operator">&</span>u<span class="token punctuation">)</span>
</code></pre>
<h5>order by 的使用</h5>
<pre><code class="prism language-go"><span class="token comment">//order by age desc</span>
db<span class="token punctuation">.</span><span class="token function">Where</span><span class="token punctuation">(</span><span class="token string">"age > ?"</span><span class="token punctuation">,</span><span class="token number">12</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Or</span><span class="token punctuation">(</span><span class="token string">"sex = ?"</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Order</span><span class="token punctuation">(</span><span class="token string">"age desc"</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Find</span><span class="token punctuation">(</span><span class="token operator">&</span>u<span class="token punctuation">)</span>
</code></pre>
<h5>limit 的使用</h5>
<pre><code class="prism language-go"><span class="token comment">//limit 10</span>
db<span class="token punctuation">.</span><span class="token function">Not</span><span class="token punctuation">(</span><span class="token string">"name"</span><span class="token punctuation">,</span><span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token builtin">string</span><span class="token punctuation">{</span><span class="token string">"xiaoming"</span><span class="token punctuation">,</span><span class="token string">"xiaohong"</span><span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Limit</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Find</span><span class="token punctuation">(</span><span class="token operator">&</span>u<span class="token punctuation">)</span>
</code></pre>
<h5>offset 的使用</h5>
<pre><code class="prism language-go"><span class="token comment">//limit 300,10</span>
db<span class="token punctuation">.</span><span class="token function">Not</span><span class="token punctuation">(</span><span class="token string">"name"</span><span class="token punctuation">,</span><span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token builtin">string</span><span class="token punctuation">{</span><span class="token string">"xiaoming"</span><span class="token punctuation">,</span><span class="token string">"xiaohong"</span><span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Limit</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Offset</span><span class="token punctuation">(</span><span class="token number">300</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Find</span><span class="token punctuation">(</span><span class="token operator">&</span>u<span class="token punctuation">)</span>
</code></pre>
<h5>count(*)</h5>
<pre><code class="prism language-go"><span class="token comment">//count(*)</span>
<span class="token keyword">var</span> count <span class="token builtin">int</span>
db<span class="token punctuation">.</span><span class="token function">Table</span><span class="token punctuation">(</span><span class="token string">"user"</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Where</span><span class="token punctuation">(</span><span class="token string">"age > ?"</span><span class="token punctuation">,</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Count</span><span class="token punctuation">(</span><span class="token operator">&</span>count<span class="token punctuation">)</span>
</code></pre>
<p>注意：这里你在指定表名的情况下sql为：select count(*) from user where age > 0;</p>
<p>如上代码如果改为：</p>
<pre><code class="prism language-go"><span class="token keyword">var</span> count <span class="token builtin">int</span>
<span class="token keyword">var</span> user <span class="token punctuation">[</span><span class="token punctuation">]</span>User
db<span class="token punctuation">.</span><span class="token function">Where</span><span class="token punctuation">(</span><span class="token string">"age > ?"</span><span class="token punctuation">,</span><span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Find</span><span class="token punctuation">(</span><span class="token operator">&</span>user<span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Count</span><span class="token punctuation">(</span><span class="token operator">&</span>count<span class="token punctuation">)</span>
</code></pre>
<p>相当于你先查出来[]User，然后统计这个list的长度。跟你预期的sql不相符。</p>
<h5>group & having</h5>
<pre><code class="prism language-go">rows<span class="token punctuation">,</span> <span class="token boolean">_</span> <span class="token operator">:=</span> db<span class="token punctuation">.</span><span class="token function">Table</span><span class="token punctuation">(</span><span class="token string">"user"</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Select</span><span class="token punctuation">(</span><span class="token string">"count(*),sex"</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Group</span><span class="token punctuation">(</span><span class="token string">"sex"</span><span class="token punctuation">)</span><span class="token punctuation">.</span>
<span class="token function">Having</span><span class="token punctuation">(</span><span class="token string">"age > ?"</span><span class="token punctuation">,</span> <span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Rows</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token keyword">for</span> rows<span class="token punctuation">.</span><span class="token function">Next</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
fmt<span class="token punctuation">.</span><span class="token function">Print</span><span class="token punctuation">(</span>rows<span class="token punctuation">.</span><span class="token function">Columns</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token punctuation">}</span>
</code></pre>
<h5>join</h5>
<pre><code class="prism language-go">db<span class="token punctuation">.</span><span class="token function">Table</span><span class="token punctuation">(</span><span class="token string">"user u"</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Select</span><span class="token punctuation">(</span><span class="token string">"u.name,u.age"</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Joins</span><span class="token punctuation">(</span><span class="token string">"left join user_ext ue on u.user_id = ue.user_id"</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Row</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
</code></pre>
<p>如果有多个连接，用多个Join方法即可。</p>
<h5>原生函数</h5>
<pre><code class="prism language-go">db<span class="token punctuation">.</span><span class="token function">Exec</span><span class="token punctuation">(</span><span class="token string">"DROP TABLE user;"</span><span class="token punctuation">)</span>
db<span class="token punctuation">.</span><span class="token function">Exec</span><span class="token punctuation">(</span><span class="token string">"UPDATE user SET name=? WHERE id IN (?)"</span><span class="token punctuation">,</span> <span class="token string">"xiaoming"</span><span class="token punctuation">,</span> <span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token builtin">int</span><span class="token punctuation">{</span><span class="token number">11</span><span class="token punctuation">,</span><span class="token number">22</span><span class="token punctuation">,</span><span class="token number">33</span><span class="token punctuation">}</span><span class="token punctuation">)</span>
db<span class="token punctuation">.</span><span class="token function">Exec</span><span class="token punctuation">(</span><span class="token string">"select * from user where id > ?"</span><span class="token punctuation">,</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Scan</span><span class="token punctuation">(</span><span class="token operator">&</span>user<span class="token punctuation">)</span>
</code></pre>
<h5>一些函数</h5>
<p><strong>FirstOrInit 和 FirstOrCreate</strong></p>
<p>获取第一个匹配的记录，若没有，则根据条件初始化一个新的记录：</p>
<pre><code>//注意：where条件只能使用Struct或者map。如果这条记录不存在，那么会新增一条name=xiaoming的记录
db.FirstOrInit(&u,User{Name:"xiaoming"})
//同上
db.FirstOrCreate(&u,User{Name:"xiaoming"})
</code></pre>
<p><strong>Attrs</strong></p>
<p>如果没有找到记录，则使用Attrs中的数据来初始化一条记录：</p>
<pre><code class="prism language-go"><span class="token comment">//使用attrs来初始化参数，如果未找到数据则使用attrs中的数据来初始化一条</span>
<span class="token comment">//注意：attrs 必须要和FirstOrInit 或者 FirstOrCreate 连用</span>
db<span class="token punctuation">.</span><span class="token function">Where</span><span class="token punctuation">(</span>User<span class="token punctuation">{</span>Name<span class="token punctuation">:</span><span class="token string">"xiaoming"</span><span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Attrs</span><span class="token punctuation">(</span>User<span class="token punctuation">{</span>Name<span class="token punctuation">:</span><span class="token string">"xiaoming"</span><span class="token punctuation">,</span>Age<span class="token punctuation">:</span><span class="token number">12</span><span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">FirstOrInit</span><span class="token punctuation">(</span><span class="token operator">&</span>u<span class="token punctuation">)</span>
</code></pre>
<p><strong>Assign</strong></p>
<pre><code class="prism language-go"><span class="token comment">//不管是否找的到，最终返回结构中都将带上Assign指定的参数</span>
db<span class="token punctuation">.</span><span class="token function">Where</span><span class="token punctuation">(</span><span class="token string">"age > 12"</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Assign</span><span class="token punctuation">(</span>User<span class="token punctuation">{</span>Name<span class="token punctuation">:</span><span class="token string">"xiaoming"</span><span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">FirstOrInit</span><span class="token punctuation">(</span><span class="token operator">&</span>u<span class="token punctuation">)</span>
</code></pre>
<p><strong>Pluck</strong></p>
<p>如果user表中你只想查询age这一列，该怎么返回呢，gorm提供了Pluck函数用于查询单列，返回数组：</p>
<pre><code class="prism language-go"><span class="token keyword">var</span> ages <span class="token punctuation">[</span><span class="token punctuation">]</span><span class="token builtin">int</span>
db<span class="token punctuation">.</span><span class="token function">Find</span><span class="token punctuation">(</span><span class="token operator">&</span>u<span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Pluck</span><span class="token punctuation">(</span><span class="token string">"age"</span><span class="token punctuation">,</span><span class="token operator">&</span>ages<span class="token punctuation">)</span>
</code></pre>
<p><strong>Scan</strong></p>
<p>Scan函数可以将结果转存储到另一个结构体中。</p>
<pre><code class="prism language-go"><span class="token keyword">type</span> SubUser <span class="token keyword">struct</span><span class="token punctuation">{</span>
Name <span class="token builtin">string</span>
Age <span class="token builtin">int</span>
<span class="token punctuation">}</span>

db<span class="token punctuation">.</span><span class="token function">Table</span><span class="token punctuation">(</span><span class="token string">"user"</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Select</span><span class="token punctuation">(</span><span class="token string">"name,age"</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Scan</span><span class="token punctuation">(</span><span class="token operator">&</span>SubUser<span class="token punctuation">)</span>
</code></pre>
<p><strong>sql.Row & sql.Rows</strong></p>
<p>row和rows用户获取查询结果。</p>
<pre><code class="prism language-go"><span class="token comment">//查询一行</span>
row <span class="token operator">:=</span> db<span class="token punctuation">.</span><span class="token function">Table</span><span class="token punctuation">(</span><span class="token string">"user"</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Where</span><span class="token punctuation">(</span><span class="token string">"name = ?"</span><span class="token punctuation">,</span> <span class="token string">"xiaoming"</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Select</span><span class="token punctuation">(</span><span class="token string">"name, age"</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Row</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token comment">// (*sql.Row)</span>
<span class="token comment">//获取一行的结果后，调用Scan方法来将返回结果赋值给对象或者结构体</span>
row<span class="token punctuation">.</span><span class="token function">Scan</span><span class="token punctuation">(</span><span class="token operator">&</span>name<span class="token punctuation">,</span> <span class="token operator">&</span>age<span class="token punctuation">)</span>

<span class="token comment">//查询多行</span>
rows<span class="token punctuation">,</span> err <span class="token operator">:=</span> db<span class="token punctuation">.</span><span class="token function">Model</span><span class="token punctuation">(</span><span class="token operator">&</span>User<span class="token punctuation">{</span><span class="token punctuation">}</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Where</span><span class="token punctuation">(</span><span class="token string">"sex = ?"</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Select</span><span class="token punctuation">(</span><span class="token string">"name, age, phone"</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Rows</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token comment">// (*sql.Rows, error)</span>
<span class="token keyword">defer</span> rows<span class="token punctuation">.</span><span class="token function">Close</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
<span class="token keyword">for</span> rows<span class="token punctuation">.</span><span class="token function">Next</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
<span class="token operator">...</span>
rows<span class="token punctuation">.</span><span class="token function">Scan</span><span class="token punctuation">(</span><span class="token operator">&</span>name<span class="token punctuation">,</span> <span class="token operator">&</span>age<span class="token punctuation">,</span> <span class="token operator">&</span>email<span class="token punctuation">)</span>
<span class="token operator">...</span>
<span class="token punctuation">}</span>
</code></pre>
<h4>日志</h4>
<p>Gorm有内置的日志记录器支持，默认情况下，它会打印发生的错误。</p>
<pre><code class="prism language-go"><span class="token comment">// 启用Logger，显示详细日志</span>
db<span class="token punctuation">.</span><span class="token function">LogMode</span><span class="token punctuation">(</span><span class="token boolean">true</span><span class="token punctuation">)</span>

<span class="token comment">// 禁用日志记录器，不显示任何日志</span>
db<span class="token punctuation">.</span><span class="token function">LogMode</span><span class="token punctuation">(</span><span class="token boolean">false</span><span class="token punctuation">)</span>

<span class="token comment">// 调试单个操作，显示此操作的详细日志</span>
db<span class="token punctuation">.</span><span class="token function">Debug</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">Where</span><span class="token punctuation">(</span><span class="token string">"name = ?"</span><span class="token punctuation">,</span> <span class="token string">"xiaoming"</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">First</span><span class="token punctuation">(</span><span class="token operator">&</span>User<span class="token punctuation">{</span><span class="token punctuation">}</span><span class="token punctuation">)</span>
</code></pre><br><br>
来源：https://www.cnblogs.com/rickiyang/p/11074162.html

頁: [1]

查看完整版本: Go orm框架gorm学习