使用openresty的lua-resty-mysql实现mysql基本操作

lua-resty-mysql是openresty中对mysql操作的封装的模块

通过lua-resty-mysql来实现对mysql的增删改查

一、前端html

  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4. <meta charset="utf-8">
  5. <title>mysql练习</title>
  6. <style type="text/css">
  7. *{margin:0;padding:0;}
  8. .wrap{margin:10px;}
  9. ul{line-height:35px;}
  10. form{margin:10px;}
  11. table{border:1px solid #ccc;width:60%;}
  12. table td{text-align:center;line-height:30px;}
  13. .mask{background:#ccc;opacity:0.5;position:absolute;top:0;left:0;width:100%;height:100%;display:none;}
  14. .dialog{background:#fff;position:absolute;left:30%;top:20%;padding:20px;border:5px solid #ccc;border-radius:5px;display:none;}
  15. </style>
  16. </head>
  17. <body>
  18. <div class="wrap">
  19. <h1>项目</h1>
  20. <form>
  21. 名称:<input type="text" name="name" id="addname">
  22. <input type="button" name="submit" value="添加" id="addBtn">
  23. </form>
  24. <table>
  25. <thead>
  26. <th>ID</th>
  27. <th>名称</th>
  28. <th>操作</th>
  29. </thead>
  30. <tbody id="tbody">
  31. </tbody>
  32. </table>
  33. </div>
  34. <div class="mask"></div>
  35. <div class="dialog">
  36. ID:<input type="text" name="id" id="id" readonly="readonly">
  37. 名称:<input type="text" name="name" id="name" >
  38. <input type="submit" name="submit" value="修改" id="updateBtn">
  39. </div>
  40. <script type="text/javascript" src="jquery.min.js"></script>
  41. <script type="text/javascript">
  42. $(function(){
  43. var $tbody = $('#tbody');
  44. function lists() {
  45. $.ajax({
  46. url:'/mysql?action=lists',
  47. type:'post',
  48. dataType:'json',
  49. success:function(res){
  50. //console.log(res);
  51. if (res.code == 200) {
  52. var tpl = '';
  53. $.each(res.data, function(k, v){
  54. tpl += '<tr><td>'+v.id+'</td><td>'+v.name+'</td><td><a href="javascript:;" data-id="'+v.id+'" class="edit">编辑</a> &nbsp;<a href="javascript:;" class="delRow" data-id="'+v.id+'">删除</a></td></tr>';
  55. });
  56. $tbody.html(tpl);
  57. } else {
  58. console.log(res.msg);
  59. }
  60. }
  61. })
  62. }
  63. lists();
  64. $('#addBtn').click(function(){
  65. var name = $('#addname').val();
  66. $.ajax({
  67. url:'/mysql?action=add',
  68. type:'post',
  69. dataType:'json',
  70. data:{name:name},
  71. success:function(res){
  72. //console.log(res);
  73. if (res.code == 200) {
  74. lists();
  75. } else {
  76. console.log(res.msg);
  77. }
  78. }
  79. })
  80. });
  81. $('#tbody').on('click', '.edit', function(){
  82. var id = $(this).attr('data-id');
  83. $.ajax({
  84. url:'/mysql?action=detail',
  85. type:'post',
  86. dataType:'json',
  87. data:{id:id},
  88. success:function(res){
  89. //console.log(res);
  90. if (res.code == 200) {
  91. $('#id').val(res.data.id);
  92. $('#name').val(res.data.name);
  93. $('.mask').show();
  94. $('.dialog').show();
  95. } else {
  96. console.log(res.msg);
  97. }
  98. }
  99. })
  100. });
  101. $(document).on('click', '#updateBtn', function(){
  102. var id = $('#id').val();
  103. var name = $('#name').val();
  104. $.ajax({
  105. url:'/mysql?action=update',
  106. type:'post',
  107. dataType:'json',
  108. data:{id:id, name:name},
  109. success:function(res){
  110. //console.log(res);
  111. if (res.code == 200) {
  112. $('.mask').hide();
  113. $('.dialog').hide();
  114. lists();
  115. } else {
  116. console.log(res.msg);
  117. }
  118. }
  119. })
  120. });
  121. $('#tbody').on('click', '.delRow', function(){
  122. var id = $(this).attr('data-id');
  123. var $row = $(this).parents('tr');
  124. $.ajax({
  125. url:'/mysql?action=delete',
  126. type:'post',
  127. dataType:'json',
  128. data:{id:id},
  129. success:function(res){
  130. //console.log(res);
  131. if (res.code == 200) {
  132. $row.remove();
  133. } else {
  134. console.log(res.msg);
  135. }
  136. }
  137. })
  138. });
  139. })
  140. </script>
  141. </body>
  142. </html>

二、nginx配置

  1. lua_package_path '/data/www/openrestyproject/mysql/lib/?.lua;/data/www/openrestyproject/mysql/?.lua;;';
  2. lua_code_cache off;
  3. server {
  4. server_name localhost;
  5. listen 8000;
  6. charset utf-8;
  7. set $LESSON_ROOT /data/www/openrestyproject/mysql;
  8. error_log /data/www/openrestyproject/mysql/logs/error.log;
  9. access_log /data/www/openrestyproject/mysql/logs/access.log main;
  10. location / {
  11. root /data/www/openrestyproject/mysql/;
  12. index index.html index.htm;
  13. }
  14. location /mysql {
  15. default_type text/html;
  16. content_by_lua_file $LESSON_ROOT/mysql.lua;
  17. }
  18. }

三、lua代码

  1. local args = ngx.req.get_uri_args()
  2. local action = args['action']
  3. local mysql = require 'resty.mysql'
  4. local cjson = require 'cjson'
  5. local db_config = {
  6. host = '192.168.137.200',
  7. port = 3306,
  8. database = 'test',
  9. user = 'root',
  10. password = '123456',
  11. table = 'tb_user'
  12. }
  13. local db, err = mysql:new()
  14. if not db then
  15. ngx.say(cjson.encode({code=500, message='未安装mysql客户端'}))
  16. return
  17. end
  18. local ok, err, errno, sqlstate = db:connect({
  19. host = db_config.host,
  20. port = db_config.port,
  21. database = db_config.database,
  22. user = db_config.user,
  23. password = db_config.password})
  24. if not ok then
  25. ngx.say(cjson.encode({code=500, message='mysql连接不上'..err}))
  26. return
  27. end
  28. function lists()
  29. local data = {}
  30. ngx.req.read_body()
  31. local posts = ngx.req.get_post_args()
  32. local page, pagesize, offset = 0, 15, 0
  33. if posts.page then
  34. page = posts.page
  35. end
  36. if posts.pagesize then
  37. pagesize = posts.pagesize
  38. end
  39. if page > 1 then
  40. offset = (page -1)*pagesize
  41. end
  42. local res, err, errno, sqlstate = db:query('SELECT * FROM `'..db_config.table..'` LIMIT '..offset..','..pagesize)
  43. if not res then
  44. ngx.say(cjson.encode({code=200, message=err, data=nil}))
  45. else
  46. ngx.say(cjson.encode({code=200, message="", data=res}))
  47. end
  48. end
  49. function add()
  50. ngx.req.read_body()
  51. local data = ngx.req.get_post_args()
  52. if data.name ~= nil then
  53. local sql = 'INSERT INTO '..db_config.table..'(name) VALUES ("'..data.name..'")';
  54. local res, err, errno, sqlstate = db:query(sql)
  55. if not res then
  56. ngx.say(cjson.encode({code=501, message="添加失败"..err..';sql:'..sql, data=nil}))
  57. else
  58. ngx.say(cjson.encode({code=200, message="添加成功", data=res.insert_id}))
  59. end
  60. else
  61. ngx.say(cjson.encode({code=501, message="参数不对", data=nil}))
  62. end
  63. end
  64. function detail()
  65. ngx.req.read_body()
  66. local post_args = ngx.req.get_post_args()
  67. if post_args.id ~= nil then
  68. local data, err, errno, sqlstate = db:query('SELECT * FROM '..db_config.table..' WHERE id='..post_args.id..' LIMIT 1', 1)
  69. local res = {}
  70. if data ~= nil then
  71. res.code = 200
  72. res.message = '请求成功'
  73. res.data = data[1]
  74. else
  75. res.code = 502
  76. res.message = '没有数据'
  77. res.data = data
  78. end
  79. ngx.say(cjson.encode(res))
  80. else
  81. ngx.say(cjson.encode({code = 501, message = '参数错误', data = nil}))
  82. end
  83. end
  84. function delete()
  85. ngx.req.read_body()
  86. local data = ngx.req.get_post_args()
  87. if data.id ~= nil then
  88. local res, err, errno, sqlstate = db:query('DELETE FROM '..db_config.table..' WHERE id='..data.id)
  89. if not res or res.affected_rows < 1 then
  90. ngx.say(cjson.encode({code = 504, message = '删除失败', data = nil}))
  91. else
  92. ngx.say(cjson.encode({code = 200, message = '修改成功', data = nil}))
  93. end
  94. else
  95. ngx.say(cjson.encode({code = 501, message = '参数错误', data = nil}))
  96. end
  97. end
  98. function update()
  99. ngx.req.read_body()
  100. local post_args = ngx.req.get_post_args()
  101. if post_args.id ~= nil and post_args.name ~= nil then
  102. local res, err, errno, sqlstate = db:query('UPDATE '..db_config.table..' SET `name` = "'..post_args.name..'" WHERE id='..post_args.id)
  103. if not res or res.affected_rows < 1 then
  104. ngx.say(cjson.encode({code = 504, message = '修改失败', data = nil}));
  105. else
  106. ngx.say(cjson.encode({code = 200, message = '修改成功', data = nil}))
  107. end
  108. else
  109. ngx.say(cjson.encode({code = 501, message = '参数错误', data = nil}));
  110. end
  111. end
  112. if action == 'lists' then
  113. lists()
  114. elseif action == 'detail' then
  115. detail()
  116. elseif action == 'add' then
  117. add()
  118. elseif action == 'delete' then
  119. delete()
  120. elseif action == 'update' then
  121. update()
  122. end

四、预览效果

项目源码地址:https://github.com/shixinke/openresty-practices/tree/master/mysql