Node.js创建PostgreSQL用户表后密码正则校验失效求助

问题出在PostgreSQL的正则解析规则上：你写的密码校验正则里用了\d来匹配数字，但PostgreSQL默认通过~操作符使用POSIX扩展正则，\d并不是POSIX正则里的有效数字匹配元字符，导致明明符合要求的密码Abcd@1234无法通过校验。

另外还要注意：JavaScript模板字符串里的反斜杠需要转义（写成\\），否则会被丢失，你原代码里的手机号正则\+和密码正则\d都存在这个隐性问题。

两种修复方式 #

方式1：改用POSIX兼容的数字匹配规则 #

把正则里的\d替换成[0-9]，同时修复反斜杠转义问题，修改后的建表语句如下：

import { pool } from '../models/pool';
const createUsersTable = `
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  firstName VARCHAR NOT NULL CHECK (LENGTH(firstName) BETWEEN 3 AND 150),
  lastName VARCHAR(86),
  userEmail TEXT NOT NULL UNIQUE CHECK (LENGTH(userEmail) BETWEEN 5 AND 280),
  mobileNumber TEXT NOT NULL UNIQUE CHECK (mobileNumber ~ '^([0|\\+[0-9]{2})?([7-9][0-9]{9})$'),
  password VARCHAR NOT NULL CHECK (password ~ '^(?=.*[a-z])(?=.*[A-Z])(?=.*[0-9])(?=.*[^0-9a-zA-Z]).{8,}$'),
  gender VARCHAR NOT NULL CHECK (gender IN ('MALE','FEMALE','OTHERS'))
  )
  `;

export const executeQueryArray = async (arr) =>
  new Promise((resolve) => {
    const stop = arr.length;
    arr.forEach(async (q, index) => {
      await pool.query(q);
      if (index + 1 === stop) resolve();
    });
  });
export const createTables = () => executeQueryArray([createUsersTable]);

（注：同时修正了createTables里的变量名错误，原代码写的createTableQueries实际应为createUsersTable）

方式2：启用Perl兼容正则模式 #

在正则开头加(?p)前缀，让PostgreSQL使用Perl风格正则，这样\d就能正常匹配数字，同时修复反斜杠转义：

import { pool } from '../models/pool';
const createUsersTable = `
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  firstName VARCHAR NOT NULL CHECK (LENGTH(firstName) BETWEEN 3 AND 150),
  lastName VARCHAR(86),
  userEmail TEXT NOT NULL UNIQUE CHECK (LENGTH(userEmail) BETWEEN 5 AND 280),
  mobileNumber TEXT NOT NULL UNIQUE CHECK (mobileNumber ~ '(?p)^([0|\\+[0-9]{2})?([7-9][0-9]{9})$'),
  password VARCHAR NOT NULL CHECK (password ~ '(?p)^(?=.*[a-z])(?=.*[A-Z])(?=.*\\d)(?=.*[^\\da-zA-Z]).{8,}$'),
  gender VARCHAR NOT NULL CHECK (gender IN ('MALE','FEMALE','OTHERS'))
  )
  `;

export const executeQueryArray = async (arr) =>
  new Promise((resolve) => {
    const stop = arr.length;
    arr.forEach(async (q, index) => {
      await pool.query(q);
      if (index + 1 === stop) resolve();
    });
  });
export const createTables = () => executeQueryArray([createUsersTable]);

修复后重新执行建表逻辑，再插入密码Abcd@1234即可通过校验。

内容的提问来源于stack exchange，提问作者Thirumalesu