React CodeMirror 中文数据库表字段SQL自动补全源提示

SQL编辑器

CodeMirror 文档 https://uiwjs.github.io/react-codemirror/

结果展示

CodeMirror 版本

{
 "@codemirror/autocomplete": "^6.17.0",
 "@codemirror/lang-sql": "^6.6.4",
 "@uiw/react-codemirror": "^4.22.0"
}

完整代码

import { autocompletion, CompletionContext } from '@codemirror/autocomplete';
import { keywordCompletionSource, MySQL, sql } from '@codemirror/lang-sql';
import ReactCodeMirror from '@uiw/react-codemirror';
import { useState } from 'react';

export const CodeMirror: React.FC = () => {

  const [value, setValue] = useState('');

  const tableSchemas: any[] = [
    { label: '中文表名1', type: 'keyword' },
    { label: '中文表名2', type: 'keyword' },
    { label: 'report_account_daily', type: 'keyword' },
    { label: 'account_id', type: 'keyword', detail: '中文表名2' },
    { label: 'account_id', type: 'keyword', detail: 'report_account_daily' },
    { label: 'created_at', type: 'keyword', detail: '中文表名2' },
    { label: 'created_at', type: 'keyword', detail: 'report_account_daily' },
    { label: 'id', type: 'keyword', detail: '中文表名1' },
    { label: 'name', type: 'keyword', detail: '中文表名1' },
    { label: '奇葩', type: 'keyword', detail: '中文表名1' },
  ];

  const tableSchema: any = {
    中文表名1: ['id', 'name', '奇葩'],
    中文表名2: ['account_id', 'created_at'],
    report_account_daily: ['account_id', 'created_at'],
  };

  const myCompletions = (context: CompletionContext) => {
    const word: any = context.matchBefore(/[\w\u4e00-\u9fa5.]+/);
    if (!word || (word?.from === word.to && !context.explicit)) return null;
    let options = [];
    if (word) {
      const parts = word.text.split('.');
      if (parts.length === 1) {
        // 提供表名的自动补全
        options = tableSchemas.map((table) => ({
          ...table,
          type: 'keyword',
        }));
      } else if (parts.length === 2) {
        // 提供列名的自动补全
        const tableName = parts[0];
        if (tableSchema[tableName]) {
          options = tableSchema[tableName].map((col: any) => ({
            label: tableName + '.' + col,
            displayLabel: col,
            type: 'keyword',
          }));
        }
      }
    }

    return {
      from: word ? word.from : context.pos,
      to: context.pos,
      options: options,
    };
  };

  const combinedCompletions = (context: CompletionContext) => {
    // 调用内置 SQL 补全源
    const sqlCompletionSource = keywordCompletionSource(MySQL);
    // 获取 SQL 补全选项
    const sqlCompletions: any = sqlCompletionSource(context);
    const customCompletion: any = myCompletions(context);

    const options = [
      ...(sqlCompletions?.options || []),
      ...(customCompletion?.options || []),
    ];

    return {
      from: Math.min(
        sqlCompletions?.from ?? context.pos,
        customCompletion?.from ?? context.pos,
      ),
      to: Math.max(
        sqlCompletions?.to ?? context.pos,
        customCompletion?.to ?? context.pos,
      ),
      options,
    };
  };

  return (
    <>
      <ReactCodeMirror
        value={value}
        theme="light"
        height="700px"
        extensions={[
          sql({ dialect: MySQL }),
          autocompletion({ override: [combinedCompletions] }),
        ]}
        onChange={(val) => setValue(val)}
        onStatistics={(data) => {
          // 光标位置
          console.log(data.selectionAsSingle.to);
        }}
      />
    </>
  );
};