作者： rain

问题

最近开发时，看到别人代码里一大段的join语句，这样做相比在应用层对主表查询后再根据关联条件查询分表有什么优缺点呢？

讨论

• 代码复用
  如果用join的话大部分代码都无法重复使用，每次都要对每个表的查询条件重新编辑，分表查胜出。

• 消耗资源不同
  用联表查询把任务都交给数据库处理，数据库处理的压力会变大，而且使用事务的话可能会锁表锁行，影响并发性能。
  而使用分表查询，由于需要与数据库多次切换，会增加与数据库IO操作的开销。

• 筛选分页
  这种情况下单表查询就很吃力了，感觉只能把分表的筛选条件作为主表的冗余字段来查询。
  不然还是用联表查询比较好了。

• 分库分表
  这种感觉就只能选择分表查询了吧？

毁了，烧毁了

是的，本来想通过L298P上的引脚驱动舵机，把ESP32的GPIO5接到L298P的ANALOG0， 然后就烧掉了，烧不进去程序了，毁了！！！

淘宝下单了两片新的ESP32，肉痛———-

总结一下：就是尽量单独给芯片供电，别用其他芯片给单片机供电。

新的开始

终于到了，加油干吧！！！

先搞一下舵机控制

这里需要通过PWM来控制舵机的角度，用的舵机是MG996R
这里参考这个页面的说明

https://components101.com/motors/mg996r-servo-motor-datasheet

参考这个图片
周期20ms, 有效占空比是1ms-2ms,1ms时0°，2ms时120°
PWM的位数设置为10,精度2^10=1024,那么有效占空比就是51-102，51时0°，102时120°

话说这里研究了好久

看看连接

然后是效果

然后是代码
初始化PWM输出参数

const int freq = 50;
const int ledChannel = 0;
const int resolution = 10;//精度1024

setup里面设置

ledcSetup(ledChannel, freq, resolution);
ledcAttachPin(ledPin, ledChannel);

loop里面控制舵机来回摇

  for(int i=0; i<120; i+=5){
    ledcWrite(ledChannel, map(i, 0, 180, 51, 102));
    get_pwm_info();
    delay(500);
  }

  for(int i=120; i>0; i-=5){
    ledcWrite(ledChannel, map(i, 0, 180, 51, 102));
    get_pwm_info();
    delay(500);
  }

完整的，之前连MQTT的先注释了，下次再加上MQTT的控制

#include <WiFi.h>
#include <Ethernet.h>
#include <PubSubClient.h>

const char* ssid     = "***";
const char* password = "***";
int currentDirection = 90;

const int freq = 50;
const int ledChannel = 0;
const int resolution = 10;//精度1024

const int ledPin = 5;

WiFiClient wifiClient;
PubSubClient client(wifiClient);

void callback(char* topic, byte* payload, unsigned int length) {
  Serial.print("Message arrived [");
  Serial.print(topic);
  Serial.print("] ");
  int signal;
  for (int i=0;i<length;i++) {
    if((char)payload[i] == 'b'){

      digitalWrite(4, HIGH);
    }

    if((char)payload[i] == 'a'){
      digitalWrite(4, LOW);
    }

    if((char)payload[i] == 'r'){

    }

    if((char)payload[i] == 'l'){

    }
    Serial.print((char)payload[i]);
  }
  Serial.println();
}

void reconnect() {
  // Loop until we're reconnected
  while (!client.connected()) {
    Serial.print("Attempting MQTT connection...");
    // Attempt to connect
    if (client.connect("arduinoClient")) {
      Serial.println("connected");
      // Once connected, publish an announcement...
      client.publish("outTopic","hello world");
      // ... and resubscribe
      client.subscribe("inTopic");
    } else {
      Serial.print("failed, rc=");
      Serial.print(client.state());
      Serial.println(" try again in 5 seconds");
      // Wait 5 seconds before retrying
      delay(5000);
    }
  }
}

void setup()
{
    Serial.begin(115200);
    delay(10);

    // We start by connecting to a WiFi network

    Serial.println(ssid);
    Serial.println(password);
    Serial.print("Connecting to ");
    Serial.println(ssid);

    WiFi.begin(ssid, password);

    while (WiFi.status() != WL_CONNECTED) {
        delay(500);
        Serial.print(".");
    }

    Serial.println("");
    Serial.println("WiFi connected");
    Serial.println("IP address: ");
    Serial.println(WiFi.localIP());

    client.setServer("*.*.*.*", 1883);
    client.setCallback(callback);

    pinMode(4, OUTPUT);

    ledcSetup(ledChannel, freq, resolution);
    ledcAttachPin(ledPin, ledChannel);
}

void loop()
{
//  if (!client.connected()) {
//    reconnect();
//  }
//
//  client.loop();

  //周期20ms, 有效占空比是1ms-2ms,1ms时0°，2ms时120°
  //精度2^10=1024,那么有效占空比就是51-102，51时0°，102时120°
  //90°时就是(51+102)/2 = 76
//  ledcWrite(ledChannel, 76);

  for(int i=0; i<120; i+=5){
    ledcWrite(ledChannel, map(i, 0, 120, 51, 102));
    get_pwm_info();
    delay(500);
  }

  for(int i=120; i>0; i-=5){
    ledcWrite(ledChannel, map(i, 0, 120, 51, 102));
    get_pwm_info();
    delay(500);
  }
}

小车

车身就直接购买了，后轮俩直流电机带动，前轮由一个舵机控制。

硬件

ESP32 连接wifi，提供网络通信能力
L298P 电机驱动，可以同时驱动两路直流电机，11V供电，然后将5V供电再提供给ESP32。

这个L298P原本是搭配UNO版的esp8266的，可以直接叠在上面的，结果装好插上电源8266就毁了，我也不知道咋回事，只能又买了个EPS32了。

软件方面

云端用mosquitto建立mqtt BROKER
ESP32上 建立mqtt客户端，通过云端broker和控制端利用发布订阅方式通信。

arduinoIDE用到的库:
WIFI — 连接wifi
SubPubClient — mqtt发布订阅客户端

实际操作

这个是接收到订阅的消息时的回调函数,这里用4引脚作为输出，当收到消息第一个字符是b时高，a时低，
然后把IO4接到驱动的蜂鸣器引脚上，就可以控制发声了（蜂鸣器的控制在D4引脚）

这里测试的话可以用一个mqtt客户端工具mqttx来进行测试

完成这一步，和小车的通信就打通了，下一步就是怎么通过消息来实现小车的控制了

void callback(char* topic, byte* payload, unsigned int length) {
  Serial.print("Message arrived [");
  Serial.print(topic);
  Serial.print("] ");
  char m = (char)payload[0];
  if(m == 'b'){
  digitalWrite(4, HIGH);
  }

  if(m == 'a'){
    digitalWrite(4, LOW);
  }
  Serial.println(m);
  Serial.println();
}

这段代码呢，就是先连wifi，然后客户端连mqtt broker

完整代码

#include <WiFi.h>
#include <Ethernet.h>
#include <PubSubClient.h>

const char* ssid     = "***";
const char* password = "***";

WiFiClient wifiClient;
PubSubClient client(wifiClient);

void callback(char* topic, byte* payload, unsigned int length) {
  Serial.print("Message arrived [");
  Serial.print(topic);
  Serial.print("] ");
  char m = (char)payload[0];
  if(m == 'b'){
  digitalWrite(4, HIGH);
  }

  if(m == 'a'){
    digitalWrite(4, LOW);
  }
  Serial.println(m);
  Serial.println();
}

void reconnect() {
  while (!client.connected()) {
    Serial.print("Attempting MQTT connection...");
    if (client.connect("arduinoClient")) {
      Serial.println("connected");
      client.publish("outTopic","hello world");
      client.subscribe("inTopic");
    } else {
      Serial.print("failed, rc=");
      Serial.print(client.state());
      Serial.println(" try again in 5 seconds");
      delay(5000);
    }
  }
}

void setup()
{
    Serial.begin(115200);
    delay(10);

    // We start by connecting to a WiFi network

    Serial.println(ssid);
    Serial.println(password);
    Serial.print("Connecting to ");
    Serial.println(ssid);

    WiFi.begin(ssid, password);

    while (WiFi.status() != WL_CONNECTED) {
        delay(500);
        Serial.print(".");
    }

    Serial.println("");
    Serial.println("WiFi connected");
    Serial.println("IP address: ");
    Serial.println(WiFi.localIP());

    client.setServer("81.*.*.*", 1883);
    client.setCallback(callback);

    pinMode(4, OUTPUT);
}

void loop()
{
  if (!client.connected()) {
    reconnect();
  }
  client.loop();
}

参考:

https://developer.mozilla.org/en-US/docs/Web/HTTP/Basics_of_HTTP/Data_URIs

参考Data_URIs的方式，把二进制文件直接显示在网页中

格式

data:[<mediatype>][;base64],<data>

• 固定前缀data:

• mediatype:文件类型,默认是text/plain;charset=US-ASCII
  可用类型参考

  https://developer.mozilla.org/en-US/docs/Web/HTTP/Basics_of_HTTP/MIME_types

例如

data:text/plain,文本数据
data:text/html,HTML代码
data:text/html;base64,base64编码的HTML代码
data:text/css,CSS代码
data:text/css;base64,base64编码的CSS代码
data:text/javascript,Javascript代码
data:text/javascript;base64,base64编码的Javascript代码
data:image/gif;base64,base64编码的gif图片数据
data:image/png;base64,base64编码的png图片数据
data:image/jpeg;base64,base64编码的jpeg图片数据
data:image/x-icon;base64,base64编码的icon图片数据

• base64
  是否通过base64编码

• ，data
  二进制文件内容

比如要显示一个png图片的二进制文件，可以先读取图片内容，做base64的编码

<img src="data:image/png ;base64, <data>">

L298P 电机驱动 shield

这个shield也不知道啥意思，怎么翻译，大概就是扩展板的意思？
买了一块L298P电机驱动的扩展板，没找到资料，搜索一下只有英文的，就翻译一下了

来源

https://electropeak.com/learn/interfacing-l298p-h-bridge-motor-driver-shield-with-arduino/
一个购买连接，不过也没找到更多资料了

• 先看看图
  

以下是说明

可以控制两台直流电机:

VMS: Module voltage(外部供电)
GND: Ground
MA1: Positive end for motor A （A电机正极）
MA2: Negative end for motor A （A电机负极）
MB1: Positive end for motor B （B电机正极）
MB2: Negative end for motor B （B电机负极）
PWMA: Speed control signal for motor A – This pin is connected to pin 10 of Arduino

A电机PWM输入 速度控制—也就是arduino的D10

PWMB: Speed control signal for motor B – This pin is connected to pin 11 of Arduino

B电机PWM输入 速度控制—也就是arduino的D11

ENA: Control signal for motor A – If HIGH, motor is in direct mode and if LOW, motor is rotating in reverse. This pin is connected to pin 12 of Arduino

A电机正反转控制—arduino的D12

ENB: Control signal for motor B – If HIGH, motor is in direct mode and if LOW, motor is rotating in reverse. This pin is connected to pin 13 of Arduino

B电机正反转控制—arduino的D13

Buzzer for make sound:
BUZ: Buzzer pin – This pin is connected to pin 4 of Arduino

蜂鸣器 D4

Connection for control servo motor:
SER: PWM pin for control servo motor – This pin is connected to pin 9 of Arduino

伺服电机（舵机） D9-PWM

Bluetooth connections:
BT2: Bluetooth Pins including +(3.3V) pins, (GND), RX (connected to D0) and TX (connected to D1)

连接蓝牙

Ultrasonic sensor connection:
ULT: connection pins to ultrasonic sensor including +(5V), (GND), Return (connected to D9) and Trigger (connected to D8)

超声波传感器

RBG LED connection:
RGB: RGB: For connection to RGB LED including pins B (connected to D6), G (connected to D5) and R (connected to D3)

RGB LED灯 R-D3 G-D5 B-D6 也不知道在哪亮。。

Other connections:
A/D: Analog and digital pins A0 to A5 for sensor and module use
D2: Digital pin 2 for sensor and module use
RS: Reset pin
GND: Ground
VCC: Board power supply – 3V, 5V

板供电

另一个地方找的示例代码，也是购买页。

https://protosupplies.com/product/l298p-motor-driver-shield/

/*
*  L298P Motor Shield
*  Code for exercising the L298P Motor Control portion of the shield
*  The low level motor control logic is kept in the function 'Motor'
*/
// The following pin designations are fixed by the shield
int const BUZZER = 4;
//  Motor A
int const ENA = 10;  
int const INA = 12;
//  Motor B
int const ENB = 11;  
int const INB = 13;

int const MIN_SPEED = 27;   // Set to minimum PWM value that will make motors turn
int const ACCEL_DELAY = 50; // delay between steps when ramping motor speed up or down.
//===============================================================================
//  Initialization
//===============================================================================
void setup()
{
  pinMode(ENA, OUTPUT);   // set all the motor control pins to outputs
  pinMode(ENB, OUTPUT);
  pinMode(INA, OUTPUT);
  pinMode(INB, OUTPUT);
  pinMode(BUZZER, OUTPUT);
  Serial.begin(9600);     // Set comm speed for serial monitor messages
}
//===============================================================================
//  Main
//===============================================================================
void loop()
{
  // Run both motors Forward at 75% power
  Motor('C', 'F', 75);   
  delay(3000);

  // Run both motors in Reverse at 75% power but sound beeper first
  Motor('C', 'F', 0);  // Stop motors
  delay(1000);
  digitalWrite(BUZZER,HIGH);delay(500);digitalWrite(BUZZER,LOW); 
  delay(500);
  digitalWrite(BUZZER,HIGH);delay(500);digitalWrite(BUZZER,LOW); 
  delay(1000);
  Motor('C', 'R', 75);  // Run motors forward at 75%
 delay(3000); 

  // now run motors in opposite directions at same time at 50% speed
  Motor('A', 'F', 50);
  Motor ('B', 'R', 50);
  delay(3000);

  // now turn off both motors
  Motor('C', 'F', 0);  
  delay(3000);

  // Run the motors across the range of possible speeds in both directions
  // Maximum speed is determined by the motor itself and the operating voltage

  // Accelerate from zero to maximum speed
  for (int i = 0; i <= 100; i++)
  {
    Motor('C', 'F', i);
    delay(ACCEL_DELAY);
  }
  delay (2000);

  // Decelerate from maximum speed to zero
  for (int i = 100; i >= 0; --i)
  {
    Motor('C', 'F', i);
    delay(ACCEL_DELAY);
  }
  delay (2000);

  // Set direction to reverse and accelerate from zero to maximum speed
  for (int i = 0; i <= 100; i++)
  {
    Motor('C', 'R', i);
    delay(ACCEL_DELAY);
  }
  delay (2000);

  // Decelerate from maximum speed to zero
  for (int i = 100; i >= 0; --i)
  {
    Motor('C', 'R', i);
    delay(ACCEL_DELAY);
  }
  // Turn off motors
  Motor('C', 'F', 0);
  delay (3000);
}
/*
 * Motor function does all the heavy lifting of controlling the motors
 * mot = motor to control either 'A' or 'B'.  'C' controls both motors.
 * dir = Direction either 'F'orward or 'R'everse
 * speed = Speed.  Takes in 1-100 percent and maps to 0-255 for PWM control.  
 * Mapping ignores speed values that are too low to make the motor turn.
 * In this case, anything below 27, but 0 still means 0 to stop the motors.
 */
void Motor(char mot, char dir, int speed)
{
  // remap the speed from range 0-100 to 0-255
  int newspeed;
  if (speed == 0)
    newspeed = 0;   // Don't remap zero, but remap everything else.
  else
    newspeed = map(speed, 1, 100, MIN_SPEED, 255);

  switch (mot) {
    case 'A':   // Controlling Motor A
      if (dir == 'F') {
        digitalWrite(INA, HIGH);
      }
      else if (dir == 'R') {
        digitalWrite(INB, LOW);
      }
      analogWrite(ENA, newspeed);
      break;

    case 'B':   // Controlling Motor B
      if (dir == 'F') {
        digitalWrite(INB, HIGH);
      }
      else if (dir == 'R') {
        digitalWrite(INB, LOW);
      }
      analogWrite(ENB, newspeed);
      break;

    case 'C':  // Controlling Both Motors
      if (dir == 'F') {
        digitalWrite(INA, HIGH);
        digitalWrite(INB, HIGH);
      }
      else if (dir == 'R') {
        digitalWrite(INA, LOW);
         digitalWrite(INB, LOW);
      }
      analogWrite(ENA, newspeed);
      analogWrite(ENB, newspeed);
      break;
  }
  // Send what we are doing with the motors out to the Serial Monitor.

  Serial.print ("Motor: ");
  if (mot=='C')
      Serial.print ("Both");
    else
      Serial.print (mot);
  Serial.print ("t Direction: ");
  Serial.print (dir);
  Serial.print ("t Speed: ");
  Serial.print (speed);
  Serial.print ("t Mapped Speed: ");
  Serial.println (newspeed);
}

问题

1、调用wx.login后code传给后端需要保存session_token,但是这个session_token无法设置缓存时间，有效时间和用户操作有关，只能通过前端wx.checksession判断有效性.
2、用户信息、手机号等授权接口解密都需要使用这个session_token。

解决方式

取消原先系统的登录验证方式，直接用checksession方法检查用户登录状态
前端直接用wx.checksession作为判断用户登录状态的方法,session无效再调用wx.login更新后端数据

流程

wx.checksession
->ok->判断用户已登录

->无效->执行->wx.login->用返回的code传给后端更新session_token

app.js

    wx.checkSession().then(
      ()=>{}, 
      ()=>{
        return utils.authLogin()
      }).finally(()=>{
        this.getUserInfo().then(res=>{
          this.globalData.userInfo = res.data;
          if(res.data.is_registered == 0){
            wx.navigateTo({
              url: '/pages/getUserProfile/getUserProfile'
            })
          } 
        })
      })

utils.js

module.exports = {
  formatTime,
  request: function(params){
    return new Promise((resolve, reject)=>{
      wx.request({
        ...params,
        dataType: 'json',
        header: {
          Authorization: 'Bearer ' + wx.getStorageSync('authToken')
        },
        success:res=>{
          if(res.data.code == 4003){
            wx.navigateTo({
              url: '/pages/index/forbidden'
            })
          }
          if(res.data.code == 403){
            this.authLogin()
          }
          resolve(res.data)
        },
        error:error=>{
          reject(error)
        }
      })
    })
  },
  login: function(){
    return new Promise((resolve, reject)=>{
      wx.login({
        success:res=>{
          resolve(res)
        },
        error:error=>{
          reject(error)
        }
      })
    })
  },
  authLogin: function(){
    this.login().then(res=>{
      this.request({
        url: 'http://www.pp.com/api/user/login',
        data: {
          code: res.code
        }
      }).then(res=>{
        wx.setStorageSync('authToken', res.data.token)
      }).catch(e=>{

      })

    })
  }
}

来源

https://arduino-esp8266.readthedocs.io/en/3.0.2/ideoptions.html
https://www.arduino.cc/en/Reference/WiFi

WIFI库

连接示例

#include <ESP8266WiFi.h>
void setup()
{
  Serial.begin(115200);
  Serial.println();

  WiFi.begin("network-name", "pass-to-network");

  Serial.print("Connecting");
  while (WiFi.status() != WL_CONNECTED)
  {
    delay(500);
    Serial.print(".");
  }
  Serial.println();

  Serial.print("Connected, IP address: ");
  Serial.println(WiFi.localIP());
}
void loop(){}

包含的class

• Station 连接wifi

• SOFT-AP 作为软路由-提供其他设备联网能力

• Scan 扫描范围内的SSID

• Client

  WiFiClient()
  connected()
  connect()
  write()
  print()
  println()
  available()
  read()
  flush()
  stop()

• WiFi Multi 记录多个ap并在断线时选择信号最好的wifi连接

  #include <ESP8266WiFiMulti.h>
  
  ESP8266WiFiMulti wifiMulti;
  // WiFi connect timeout per AP. Increase when connecting takes longer.
  const uint32_t connectTimeoutMs = 5000;
  
  void setup()
  {
    // Set in station mode
    WiFi.mode(WIFI_STA);
  
    // Register multi WiFi networks
    wifiMulti.addAP("ssid_from_AP_1", "your_password_for_AP_1");
    wifiMulti.addAP("ssid_from_AP_2", "your_password_for_AP_2");
    wifiMulti.addAP("ssid_from_AP_3", "your_password_for_AP_3");
  }
  
  void loop()
  {
    // Maintain WiFi connection
    if (wifiMulti.run(connectTimeoutMs) == WL_CONNECTED) {
        ...
    }
  }

• Server
  例子

  #include <ESP8266WiFi.h>
  
  const char* ssid = "********";
  const char* password = "********";
  
  WiFiServer server(80);
  
  void setup()
  {
    Serial.begin(115200);
    Serial.println();
  
    Serial.printf("Connecting to %s ", ssid);
    WiFi.begin(ssid, password);
    while (WiFi.status() != WL_CONNECTED)
    {
      delay(500);
      Serial.print(".");
    }
    Serial.println(" connected");
  
    server.begin();
    Serial.printf("Web server started, open %s in a web browser\n", WiFi.localIP().toString().c_str());
  }
  
  // prepare a web page to be send to a client (web browser)
  String prepareHtmlPage()
  {
    String htmlPage;
    htmlPage.reserve(1024);               // prevent ram fragmentation
    htmlPage = F("HTTP/1.1 200 OK\r\n"
                 "Content-Type: text/html\r\n"
                 "Connection: close\r\n"  // the connection will be closed after completion of the response
                 "Refresh: 5\r\n"         // refresh the page automatically every 5 sec
                 "\r\n"
                 "<!DOCTYPE HTML>"
                 "<html>"
                 "Analog input:  ");
    htmlPage += analogRead(A0);
    htmlPage += F("</html>"
                  "\r\n");
    return htmlPage;
  }
  
  void loop()
  {
    WiFiClient client = server.available();
    // wait for a client (web browser) to connect
    if (client)
    {
      Serial.println("\n[Client connected]");
      while (client.connected())
      {
        // read line by line what the client (web browser) is requesting
        if (client.available())
        {
          String line = client.readStringUntil('\r');
          Serial.print(line);
          // wait for end of client's request, that is marked with an empty line
          if (line.length() == 1 && line[0] == '\n')
          {
            client.println(prepareHtmlPage());
            break;
          }
        }
      }
  
      while (client.available()) {
        // but first, let client finish its request
        // that's diplomatic compliance to protocols
        // (and otherwise some clients may complain, like curl)
        // (that is an example, prefer using a proper webserver library)
        client.read();
      }
  
      // close the connection:
      client.stop();
      Serial.println("[Client disconnected]");
    }
  }

• UDP UDP通信

  begin()
  available()
  beginPacket()
  endPacket()
  write()
  parsePacket()
  peek()
  read()
  flush()
  stop()
  remoteIP()
  remotePort()

wget --mirror --convert-links --adjust-extension --page-requisites 
--no-parent http://example.org

• –mirror – Makes (among other things) the download recursive.
• –convert-links – convert all the links (also to stuff like CSS stylesheets) to relative, so it will be suitable for offline viewing.
• –adjust-extension – Adds suitable extensions to filenames (html or css) depending on their content-type.
• –page-requisites – Download things like CSS style-sheets and images required to properly display the page offline.
• –no-parent – When recursing do not ascend to the parent directory. It useful for restricting the download to only a portion of the site.

简写

wget -mkEpnp http://example.org

wget 准确率不高，HTTrack是更好用的工具

https://www.httrack.com/
HTTrack is a free (GPL, libre/free software) and easy-to-use offline browser utility.