自定义一个SqlEditor

基于Vue+ElementPlus，自定义一个SqlEditor，支持提示词跟随。

<template>
  <div class="sql-editor">
    <el-input
      type="textarea"
      v-model="sqlText"
      @input="handleInput"
      @keydown.tab.prevent="handleTab"
      @keydown.down.prevent="moveDown"
      @keydown.up.prevent="moveUp"
      @keydown.enter.prevent="handleEnter"
      @keydown.esc.prevent.stop="handleEsc"
      ref="textareaRef"
      :autosize="{ minRows: props.rows }"
      class="custom-textarea"
      :placeholder="props.placeholder"
      resize="none"
    />
    <div
      v-if="showSuggestions"
      class="suggestion-list"
      :style="{ left: `${left}px`, top: `${top}px`, zIndex: 2000 }"
    >
      <div
        style="width: 100%; display: flex; justify-content: space-between"
        v-for="(item, index) in filteredSuggestions"
        :key="index"
        @click="selectSuggestion(item)"
        :class="{ active: index === activeIndex }"
      >
        <span>{{ item.name }}</span>
        <span style="color: var(--el-text-color-secondary); font-size: 13px">
          {{ item.desc }}
        </span>
      </div>
    </div>
  </div>
</template>

<script setup>
import { ref, computed, nextTick } from 'vue'

const props = defineProps({
  schema: ref([]),
  placeholder: ref(''),
  rows: ref('1'),
  modelValue: ref(''),
  resize: ref('none'),
})

//常用的sql关键字
const sql_keys = [
  {
    name: 'SELECT',
    desc: '从数据库中选择数据',
  },
  {
    name: 'FROM',
    desc: '指定要查询的表',
  },
  {
    name: 'WHERE',
    desc: '筛选记录的条件',
  },
  {
    name: 'GROUP BY',
    desc: '根据一个或多个列对结果集进行分组',
  },
  {
    name: 'HAVING',
    desc: '对分组结果进行过滤',
  },
  {
    name: 'ORDER BY',
    desc: '对结果集进行排序',
  },
  {
    name: 'JOIN',
    desc: '基于相关列连接多个表',
  },
  {
    name: 'INNER JOIN',
    desc: '返回两表中匹配的行',
  },
  {
    name: 'LEFT JOIN',
    desc: '返回左表所有行和右表匹配行',
  },
  {
    name: 'RIGHT JOIN',
    desc: '返回右表所有行和左表匹配行',
  },
  {
    name: 'FULL JOIN',
    desc: '返回两表中所有行',
  },
  {
    name: 'UNION',
    desc: '合并两个或多个SELECT语句的结果集',
  },
  {
    name: 'DISTINCT',
    desc: '返回唯一不同的值',
  },
  {
    name: 'COUNT',
    desc: '返回行数',
  },
  {
    name: 'SUM',
    desc: '返回数值列的总和',
  },
  {
    name: 'AVG',
    desc: '返回数值列的平均值',
  },
  {
    name: 'MIN',
    desc: '返回列的最小值',
  },
  {
    name: 'MAX',
    desc: '返回列的最大值',
  },
  {
    name: 'AND',
    desc: '逻辑与操作符（所有条件必须都为真）',
  },
  {
    name: 'OR',
    desc: '逻辑或操作符（任一条件为真即可）',
  },
  {
    name: 'NOT',
    desc: '逻辑非操作符，用于否定条件',
  },
  {
    name: 'IN',
    desc: '检查值是否在指定列表中',
  },
  {
    name: 'BETWEEN',
    desc: '检查值是否在某个范围内',
  },
  {
    name: 'EXISTS',
    desc: '检查子查询是否返回结果',
  },
  {
    name: 'LIKE',
    desc: '模式匹配操作符',
  },
  {
    name: 'IS NULL',
    desc: '检查值是否为NULL',
  },
]

//所有建议词
const allKeys = computed(() => {
  let keys = sql_keys
  props.schema.forEach((element) => {
    keys.push({
      name: element.name,
      desc: element.desc,
    })
  })
  return keys
})

const emit = defineEmits(['update:modelValue'])

// 中转变量（可处理修饰符或格式化）
const sqlText = computed({
  get: () => props.modelValue,
  set: (val) => emit('update:modelValue', val),
})

const textareaRef = ref(null)
const showSuggestions = ref(false)
const activeIndex = ref(0)
const lastCursorPos = ref(0)
const top = ref(0) //提示框的位置top
const left = ref(0) //提示框的位置left

// 当前输入的关键字（根据光标位置提取）
const currentKeyword = computed(() => {
  const text = textareaRef.value?.textarea.value
  const pos = lastCursorPos.value
  let start = pos - 1
  while (start >= 0 && /[\w.]/.test(text[start])) {
    start--
  }
  return text.substring(start + 1, pos)
})

// 过滤后的提示列表
const filteredSuggestions = computed(() => {
  const keyword = currentKeyword.value.toLowerCase()
  if (keyword && keyword != '') {
    const suggestions = allKeys.value.filter((item) => item.name.toLowerCase().includes(keyword))
    return suggestions
  }
  return []
})

/**
 * 获取坐标
 * @param divElement
 */
function getLastCharPosition(divElement) {
  // 1. 确保div有内容
  if (!divElement.textContent.trim()) return null

  // 2. 创建Range对象
  const range = document.createRange()
  const textNode = divElement.firstChild // 假设只有文本节点
  const textLength = textNode.length

  // 3. 设置Range到最后一个字符
  range.setStart(textNode, textLength - 1)
  range.setEnd(textNode, textLength)

  // 4. 获取字符位置
  const rect = range.getBoundingClientRect()
  const divRect = divElement.getBoundingClientRect()

  // 5. 计算相对坐标
  return {
    x: rect.left - divRect.left,
    y: rect.top - divRect.top,
  }
}

/**
 * 获取光标相对于textarea的坐标偏移量
 * @param textarea
 */
const getCursorCoordinates = (textarea) => {
  // 1. 获取光标位置
  const cursorPos = textarea.selectionStart

  // 2. 创建测量元素
  const mirror = document.createElement('div')
  mirror.style.cssText = `
    position: absolute;
    visibility: hidden;
    white-space: pre-wrap;
    word-wrap: break-word;
    font-family: ${window.getComputedStyle(textarea).fontFamily};
    font-size: ${window.getComputedStyle(textarea).fontSize};
    line-height: ${window.getComputedStyle(textarea).lineHeight};
    width: ${textarea.clientWidth}px;
    padding: ${window.getComputedStyle(textarea).padding};
  `
  // 3. 设置测量内容（光标前的文本）
  mirror.textContent = textarea.value.substring(0, cursorPos)
  document.body.appendChild(mirror)

  //4.获取最后一个元素的坐标
  const pos = getLastCharPosition(mirror)
  // 5. 清理
  document.body.removeChild(mirror)

  return {
    top: (pos && pos.y) || 0,
    left: (pos && pos.x) || 0,
  }
}

// 输入处理
const handleInput = () => {
  // 获取原生textarea元素
  const textarea = textareaRef.value?.textarea
  lastCursorPos.value = textarea.selectionStart
  showSuggestions.value = filteredSuggestions.value.length > 0
  activeIndex.value = 0
  if (showSuggestions.value) {
    const cord = getCursorCoordinates(textarea)
    top.value = cord.top + 20
    left.value = cord.left
  }
}

// 选择提示项
const selectSuggestion = (item) => {
  showSuggestions.value = false
  insertAtCursor(item.name)
}

// 在光标位置插入文本
const insertAtCursor = (text) => {
  const textarea = textareaRef.value?.textarea
  const start = textarea.selectionStart
  const end = textarea.selectionEnd
  const before = sqlText.value.substring(0, start - currentKeyword.value.length)
  const after = sqlText.value.substring(end)
  sqlText.value = before + text + after
  // 移动光标到插入位置后
  nextTick(() => {
    textarea.selectionStart = before.length + text.length
    textarea.selectionEnd = before.length + text.length
  })
}

// 键盘事件处理

/**
 * 处理Tab
 */
const handleTab = () => {
  if (showSuggestions.value) {
    selectSuggestion(filteredSuggestions.value[activeIndex.value])
  } else {
    insertAtCursor('\t')
  }
}

/**
 * 处理Enter
 */
const handleEnter = () => {
  if (showSuggestions.value) {
    selectSuggestion(filteredSuggestions.value[activeIndex.value])
  } else {
    insertAtCursor('\n')
  }
}

/**
 * 响应ESC
 */
const handleEsc = () => {
  if (showSuggestions.value) {
    showSuggestions.value = false
    return
  }
}

/**
 * 向下
 */
const moveDown = () => {
  if (activeIndex.value < filteredSuggestions.value.length - 1) {
    activeIndex.value++
  }
}

/**
 * 向上
 */
const moveUp = () => {
  if (activeIndex.value > 0) {
    activeIndex.value--
  }
}
</script>

<style scoped>
.sql-editor {
  position: relative;
}

.suggestion-list {
  width: 320px;
  position: absolute;
  border: 1px solid #dcdfe6;
  background: white;
  z-index: 2000;
  max-height: 200px;
  overflow-y: auto;
}

.suggestion-list div {
  padding: 8px 16px;
  cursor: pointer;
}

.suggestion-list div:hover,
.suggestion-list div.active {
  background: #f5f7fa;
}
</style>