fix(sql): 储存证 ACL 配置按钮授权给全部角色

迁移脚本为各租户有效角色补齐 storage-cert-qc-acl 菜单，使质检/生产权限入口对全部角色可见。

Co-authored-by: Cursor <cursoragent@cursor.com>

backend/app/core/db_collation.py

@@ -9,7 +9,19 @@ def unicode_ci(col):
 
 
 def eq_unicode(left, right):
-    """跨表字符串等值比较（JOIN / WHERE）时统一为 utf8mb4_unicode_ci。"""
+    """跨表字符串等值比较（JOIN / WHERE）时统一为 utf8mb4_unicode_ci。
+
+    列与列：两侧 collate。列与字面量：仅列侧 collate，避免连接 collation
+    （常见 utf8mb4_general_ci）与列 collation（utf8mb4_unicode_ci）混用。
+    """
+    left_is_str = isinstance(left, str)
+    right_is_str = isinstance(right, str)
+    if left_is_str and right_is_str:
+        return left == right
+    if left_is_str:
+        return left == unicode_ci(right)
+    if right_is_str:
+        return unicode_ci(left) == right
     return unicode_ci(left) == unicode_ci(right)
 
 

backend/app/crud/sample_storage_cert_qc_acl.py

@@ -6,6 +6,7 @@ from typing import Any, Dict, List, Optional, Set
 
 from sqlalchemy.orm import Session
 
+from app.core.db_collation import eq_unicode, unicode_ci
 from app.core.utils import generate_uuid
 from app.crud.child_row_sync import sync_child_rows
 from app.models.models import BizStorageCertQcAcl, BizStorageCertQcAclUser, SysUser
@@ -22,8 +23,8 @@ def is_acl_strict(db: Session, tenant_no: str) -> bool:
     row = (
         db.query(BizStorageCertQcAcl.id)
         .filter(
-            BizStorageCertQcAcl.tenant_no == tenant_no,
-            BizStorageCertQcAcl.configured_flag == "1",
+            eq_unicode(BizStorageCertQcAcl.tenant_no, tenant_no),
+            eq_unicode(BizStorageCertQcAcl.configured_flag, "1"),
             BizStorageCertQcAcl.del_flag == 0,
         )
         .first()
@@ -48,8 +49,8 @@ def resolve_editable_field_keys(
     acls = (
         db.query(BizStorageCertQcAcl)
         .filter(
-            BizStorageCertQcAcl.tenant_no == tenant_no,
-            BizStorageCertQcAcl.configured_flag == "1",
+            eq_unicode(BizStorageCertQcAcl.tenant_no, tenant_no),
+            eq_unicode(BizStorageCertQcAcl.configured_flag, "1"),
             BizStorageCertQcAcl.del_flag == 0,
         )
         .all()
@@ -60,9 +61,9 @@ def resolve_editable_field_keys(
     membership = (
         db.query(BizStorageCertQcAclUser.acl_id)
         .filter(
-            BizStorageCertQcAclUser.tenant_no == tenant_no,
-            BizStorageCertQcAclUser.acl_id.in_(acl_ids),
-            BizStorageCertQcAclUser.user_id == uid,
+            eq_unicode(BizStorageCertQcAclUser.tenant_no, tenant_no),
+            unicode_ci(BizStorageCertQcAclUser.acl_id).in_(acl_ids),
+            eq_unicode(BizStorageCertQcAclUser.user_id, uid),
             BizStorageCertQcAclUser.del_flag == 0,
         )
         .all()
@@ -115,8 +116,8 @@ def _validate_user_ids(db: Session, tenant_no: str, user_ids: Any) -> List[str]:
     rows = (
         db.query(SysUser.id)
         .filter(
-            SysUser.tenant_no == tenant_no,
-            SysUser.id.in_(out),
+            eq_unicode(SysUser.tenant_no, tenant_no),
+            unicode_ci(SysUser.id).in_(out),
             SysUser.del_flag == 0,
         )
         .all()
@@ -133,8 +134,8 @@ def get_acl_detail(db: Session, tenant_no: str, scope: str) -> Dict[str, Any]:
     row = (
         db.query(BizStorageCertQcAcl)
         .filter(
-            BizStorageCertQcAcl.tenant_no == tenant_no,
-            BizStorageCertQcAcl.scope == scope_key,
+            eq_unicode(BizStorageCertQcAcl.tenant_no, tenant_no),
+            eq_unicode(BizStorageCertQcAcl.scope, scope_key),
             BizStorageCertQcAcl.del_flag == 0,
         )
         .first()
@@ -152,11 +153,11 @@ def get_acl_detail(db: Session, tenant_no: str, scope: str) -> Dict[str, Any]:
         db.query(BizStorageCertQcAclUser, SysUser)
         .outerjoin(
             SysUser,
-            (SysUser.id == BizStorageCertQcAclUser.user_id) & (SysUser.del_flag == 0),
+            eq_unicode(SysUser.id, BizStorageCertQcAclUser.user_id) & (SysUser.del_flag == 0),
         )
         .filter(
-            BizStorageCertQcAclUser.tenant_no == tenant_no,
-            BizStorageCertQcAclUser.acl_id == row.id,
+            eq_unicode(BizStorageCertQcAclUser.tenant_no, tenant_no),
+            eq_unicode(BizStorageCertQcAclUser.acl_id, row.id),
             BizStorageCertQcAclUser.del_flag == 0,
         )
         .order_by(BizStorageCertQcAclUser.order_num.asc(), BizStorageCertQcAclUser.create_time.asc())
@@ -202,8 +203,8 @@ def upsert_acl(
     row = (
         db.query(BizStorageCertQcAcl)
         .filter(
-            BizStorageCertQcAcl.tenant_no == tenant_no,
-            BizStorageCertQcAcl.scope == scope_key,
+            eq_unicode(BizStorageCertQcAcl.tenant_no, tenant_no),
+            eq_unicode(BizStorageCertQcAcl.scope, scope_key),
             BizStorageCertQcAcl.del_flag == 0,
         )
         .first()

backend/tests/test_sample_storage_cert_qc_acl.py

@@ -1,6 +1,11 @@
 """储存证质检字段 ACL：合法字段与 scope。"""
 import unittest
+from unittest.mock import MagicMock
 
+from sqlalchemy.dialects import mysql
+
+from app.crud import sample_storage_cert_qc_acl as acl_svc
+from app.models.models import BizStorageCertQcAcl, BizStorageCertQcAclUser, SysUser
 from app.services.sample_storage_cert_qc import (
     ACL_SCOPE_PRODUCTION,
     ACL_SCOPE_QC,
@@ -31,5 +36,66 @@ class TestAclFieldKeys(unittest.TestCase):
         self.assertEqual(ACL_SCOPE_PRODUCTION, "production")
 
 
+class TestAclUserJoinCollation(unittest.TestCase):
+    def test_detail_user_join_uses_unicode_collate(self):
+        """JOIN sys_user 时统一 utf8mb4_unicode_ci，避免 1267 mix of collations。"""
+        acl = MagicMock()
+        acl.id = "acl-1"
+        acl.field_keys = "[]"
+        acl.configured_flag = "1"
+
+        captured = {}
+
+        class _Query:
+            def __init__(self, *args, **kwargs):
+                pass
+
+            def filter(self, *args, **kwargs):
+                captured.setdefault("filters", []).extend(args)
+                return self
+
+            def first(self):
+                return acl
+
+            def outerjoin(self, *args, **kwargs):
+                captured["join_on"] = args[1] if len(args) > 1 else None
+                return self
+
+            def order_by(self, *args, **kwargs):
+                return self
+
+            def all(self):
+                return []
+
+        db = MagicMock()
+        db.query.side_effect = lambda *args, **kwargs: _Query()
+
+        detail = acl_svc.get_acl_detail(db, "t1", "qc")
+        self.assertEqual(detail["scope"], "qc")
+        join_on = captured.get("join_on")
+        self.assertIsNotNone(join_on)
+        sql = str(
+            join_on.compile(dialect=mysql.dialect(), compile_kwargs={"literal_binds": True})
+        ).lower()
+        self.assertIn("utf8mb4_unicode_ci", sql)
+        filter_sql = " ".join(
+            str(f.compile(dialect=mysql.dialect(), compile_kwargs={"literal_binds": True})).lower()
+            for f in captured.get("filters", [])
+        )
+        self.assertIn("utf8mb4_unicode_ci", filter_sql)
+        self.assertIn("sys_user", SysUser.__tablename__)
+        self.assertIn("biz_storage_cert_qc_acl_user", BizStorageCertQcAclUser.__tablename__)
+        self.assertEqual(BizStorageCertQcAcl.__tablename__, "biz_storage_cert_qc_acl")
+
+    def test_eq_unicode_literal_only_collates_column(self):
+        from app.core.db_collation import eq_unicode
+
+        expr = eq_unicode(BizStorageCertQcAcl.tenant_no, "t1")
+        sql = str(
+            expr.compile(dialect=mysql.dialect(), compile_kwargs={"literal_binds": True})
+        ).lower()
+        self.assertIn("utf8mb4_unicode_ci", sql)
+        self.assertEqual(sql.count("utf8mb4_unicode_ci"), 1)
+
 if __name__ == "__main__":
     unittest.main()

docs/sql/migrations/2026-11-11-storage-cert-qc-acl-collation-fix.sql

@@ -0,0 +1,14 @@
+-- Issue #97：将储存证质检 ACL 表 collation 对齐库默认 utf8mb4_unicode_ci。
+-- 修复与 sys_user 等表 JOIN 时 Illegal mix of collations。可重复执行。
+
+ALTER TABLE biz_storage_cert_qc_acl
+  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+
+ALTER TABLE biz_storage_cert_qc_acl_user
+  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
+
+SELECT table_name, table_collation
+FROM information_schema.tables
+WHERE table_schema = DATABASE()
+  AND table_name IN ('biz_storage_cert_qc_acl', 'biz_storage_cert_qc_acl_user')
+ORDER BY table_name;

docs/sql/migrations/2026-11-11-storage-cert-qc-field-acl.sql

@@ -1,5 +1,6 @@
 -- Issue #93：储存证质检信息字段级 ACL（质检/生产）。MySQL 5.7，可重复执行。
--- 不自动授予任何角色；管理员在角色管理中勾选「储存证质检字段权限配置」。
+-- 菜单按钮「储存证质检字段权限配置」控制弹窗 footer 的「质检权限」「生产权限」入口；
+-- 本脚本会将该按钮授权给当前租户下全部有效角色。
 
 CREATE TABLE IF NOT EXISTS biz_storage_cert_qc_acl (
   id VARCHAR(64) NOT NULL,
@@ -17,7 +18,7 @@ CREATE TABLE IF NOT EXISTS biz_storage_cert_qc_acl (
   del_flag SMALLINT NOT NULL DEFAULT 0,
   PRIMARY KEY (id),
   KEY ix_tenant_scope (tenant_no, scope, del_flag)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
 CREATE TABLE IF NOT EXISTS biz_storage_cert_qc_acl_user (
   id VARCHAR(64) NOT NULL,
@@ -35,7 +36,7 @@ CREATE TABLE IF NOT EXISTS biz_storage_cert_qc_acl_user (
   PRIMARY KEY (id),
   KEY ix_tenant_acl (tenant_no, acl_id, del_flag),
   KEY ix_tenant_user (tenant_no, user_id, del_flag)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
 INSERT INTO sys_menu (
     id, menu_name, parent_id, menu_type, path, component, perms,
@@ -60,6 +61,48 @@ WHERE parent.del_flag = 0 AND parent.status = '1'
         AND existing.del_flag = 0
   );
 
+-- 给所有有效角色授予「质检权限 / 生产权限」配置按钮（同一菜单码）
+INSERT INTO sys_role_menu (
+    id, role_id, menu_id, tenant_no, order_num, remark,
+    create_by, create_dept_id, create_time, update_by, update_time, del_flag
+)
+SELECT
+    INSERT(INSERT(MD5(CONCAT('storage-cert-qc-acl-role:', r.tenant_no, ':', r.id, ':', m.id)),
+                  13, 1, '3'), 17, 1, '8'),
+    r.id,
+    m.id,
+    r.tenant_no,
+    NULL,
+    'migration: grant storage-cert-qc-acl to all roles',
+    'migration',
+    '',
+    NOW(),
+    'migration',
+    NOW(),
+    0
+FROM sys_role AS r
+JOIN sys_menu AS m
+  ON m.tenant_no = r.tenant_no
+ AND m.perms = 'cell-storage:sample-info:storage-cert-qc-acl'
+ AND m.del_flag = 0
+ AND m.status = '1'
+WHERE r.del_flag = 0
+  AND NOT EXISTS (
+      SELECT 1 FROM sys_role_menu AS existing
+      WHERE existing.role_id = r.id
+        AND existing.menu_id = m.id
+        AND existing.tenant_no = r.tenant_no
+        AND existing.del_flag = 0
+  );
+
 SELECT tenant_no, parent_id, id, menu_name, perms, status
 FROM sys_menu
 WHERE perms = 'cell-storage:sample-info:storage-cert-qc-acl' AND del_flag = 0;
+
+SELECT r.tenant_no, r.role_name, m.perms, rm.id AS role_menu_id
+FROM sys_role_menu AS rm
+JOIN sys_role AS r ON r.id = rm.role_id AND r.tenant_no = rm.tenant_no AND r.del_flag = 0
+JOIN sys_menu AS m ON m.id = rm.menu_id AND m.tenant_no = rm.tenant_no AND m.del_flag = 0
+WHERE m.perms = 'cell-storage:sample-info:storage-cert-qc-acl'
+  AND rm.del_flag = 0
+ORDER BY r.tenant_no, r.role_name;

web-admin/src/components/business/storage-cert-qc-acl-dialog/index.vue

@@ -13,9 +13,19 @@
     <div v-loading="loading" class="storage-cert-qc-acl-dialog__body">
       <el-tabs v-model="activeTab">
         <el-tab-pane label="勾选可编辑字段" name="fields">
+          <div class="field-toolbar">
+            <el-button @click="selectAllFields">
+              <el-icon><Select /></el-icon>
+              全选
+            </el-button>
+            <el-button @click="clearAllFields">
+              <el-icon><RefreshLeft /></el-icon>
+              取消全选
+            </el-button>
+          </div>
           <div v-for="(group, gi) in fieldGroups" :key="gi" class="field-block">
             <div class="field-block__title">{{ group.title }}</div>
-            <el-checkbox-group v-model="selectedKeys">
+            <el-checkbox-group v-model="selectedKeys" class="field-check-grid">
               <el-checkbox
                 v-for="field in group.fields"
                 :key="field.key"
@@ -66,7 +76,7 @@
 </template>
 
 <script setup lang="ts">
-  import { Check, Close } from '@element-plus/icons-vue'
+  import { Check, Close, RefreshLeft, Select } from '@element-plus/icons-vue'
   import { ElMessage } from 'element-plus'
   import { fetchUserList } from '@/api/admin'
   import {
@@ -113,6 +123,40 @@
     return Array.from(map.values())
   })
 
+  const allFieldKeys = computed(() => {
+    const keys: string[] = []
+    const seen = new Set<string>()
+    for (const group of fieldGroups.value) {
+      for (const field of group.fields) {
+        if (seen.has(field.key)) continue
+        seen.add(field.key)
+        keys.push(field.key)
+      }
+    }
+    return keys
+  })
+
+  function selectAllFields() {
+    selectedKeys.value = [...allFieldKeys.value]
+  }
+
+  function clearAllFields() {
+    selectedKeys.value = []
+  }
+
+  function formatUserOption(row: {
+    id?: string
+    nickName?: string
+    userName?: string
+    userNo?: string
+  }) {
+    const id = String(row.id || '')
+    if (!id) return null
+    const name = String(row.nickName || row.userName || row.userNo || id)
+    const account = String(row.userName || row.userNo || '')
+    return { value: id, label: account ? `${name}（${account}）` : name }
+  }
+
   function upsertUserOption(id: string, label: string) {
     if (!id) return
     if (userOptions.value.some((u) => u.value === id)) return
@@ -126,15 +170,32 @@
         page: 1,
         pageSize: 50,
         status: '1',
-        keyword: keyword || undefined
+        keyword: (keyword || '').trim() || undefined
       })
       const list = (res as any)?.list || []
+      const next: Array<{ label: string; value: string }> = []
+      const seen = new Set<string>()
       for (const row of list) {
-        const id = String(row.id || '')
-        const name = String(row.nickName || row.userName || row.userNo || id)
-        const account = String(row.userName || row.userNo || '')
-        upsertUserOption(id, account ? `${name}（${account}）` : name)
+        const opt = formatUserOption(row)
+        if (!opt || seen.has(opt.value)) continue
+        seen.add(opt.value)
+        next.push(opt)
       }
+      // 已选人员不在本页结果中时保留，避免标签丢失且不影响远程模糊检索展示
+      for (const id of selectedUserIds.value) {
+        if (seen.has(id)) continue
+        const keep = userOptions.value.find((u) => u.value === id)
+        if (keep) {
+          seen.add(id)
+          next.unshift(keep)
+        }
+      }
+      userOptions.value = next
+    } catch {
+      // 检索失败时仍保留已选项，避免清空已配置人员标签
+      userOptions.value = userOptions.value.filter((u) =>
+        selectedUserIds.value.includes(u.value)
+      )
     } finally {
       userLoading.value = false
     }
@@ -149,14 +210,15 @@
       selectedUserIds.value = Array.isArray(detail?.userIds) ? [...detail.userIds] : []
       userOptions.value = []
       for (const u of detail?.users || []) {
-        const id = String(u.id || '')
-        const name = String(u.nickName || u.userName || id)
-        const account = String(u.userName || '')
-        upsertUserOption(id, account ? `${name}（${account}）` : name)
-      }
-      if (!userOptions.value.length) {
-        await searchUsers('')
+        const opt = formatUserOption({
+          id: u.id,
+          nickName: u.nickName,
+          userName: u.userName
+        })
+        if (opt) upsertUserOption(opt.value, opt.label)
       }
+      // 预拉一批启用用户；真正按姓名/账号模糊检索在输入时由 remote-method 触发
+      await searchUsers('')
     } catch {
       selectedKeys.value = []
       selectedUserIds.value = []
@@ -193,6 +255,13 @@
     min-height: 280px;
   }
 
+  .field-toolbar {
+    display: flex;
+    flex-wrap: wrap;
+    gap: 8px;
+    margin-bottom: 12px;
+  }
+
   .field-block {
     margin-bottom: 14px;
   }
@@ -204,10 +273,31 @@
     color: var(--el-color-primary-dark-2, #059669);
   }
 
+  .field-check-grid {
+    display: grid;
+    grid-template-columns: repeat(2, minmax(0, 1fr));
+    gap: 6px 16px;
+    width: 100%;
+  }
+
   .field-check {
     display: flex;
+    align-items: flex-start;
     margin-right: 0;
-    margin-bottom: 6px;
+    margin-bottom: 0;
+    height: auto;
+    white-space: normal;
+
+    :deep(.el-checkbox__label) {
+      white-space: normal;
+      line-height: 1.4;
+    }
+  }
+
+  @media (max-width: 560px) {
+    .field-check-grid {
+      grid-template-columns: 1fr;
+    }
   }
 
   .form-tip {