Lucanlee/coolcoth.com
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
coolcoth.com/install/schema.sql
T
Lucanlee 4d36a284ff perf: V0.9.1-0.9.3 性能优化（count/featured 全表→聚合、请求内缓存、二级索引、PSI 未读铃铛） 
- V0.9.1 (P0): Model::count() 改 SELECT COUNT(*)；新增请求内 identity-map 缓存；
  Product::featured() 改 WHERE+ LIMIT 避免全表
- V0.9.2 (P1): schema.sql 内联二级索引 + install/upgrades/009_perf_indexes.sql 幂等迁移
- V0.9.3 (P1): psi_unread_events() 改 COUNT + JSON_CONTAINS，消除每页 all()+循环
2026-08-08 19:47:35 +08:00

341 lines
11 KiB
SQL
Raw Permalink Blame History

-- 酷冰甲降温服官网 MySQL 结构
SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS `categories` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(120) NOT NULL,
`slug` VARCHAR(120) DEFAULT '',
`icon` VARCHAR(20) DEFAULT '',
`description` TEXT,
`sort_order` INT DEFAULT 0,
`status` TINYINT DEFAULT 1,
`layout` TEXT,
`mode` VARCHAR(16) NOT NULL DEFAULT 'fixed',
KEY idx_categories_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `products` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`category_id` INT DEFAULT 0,
`name` VARCHAR(200) NOT NULL,
`slug` VARCHAR(200) DEFAULT '',
`cover` VARCHAR(255) DEFAULT '',
`summary` TEXT,
`description` TEXT,
`price` DECIMAL(10,2) DEFAULT 0,
`specs` TEXT,
`gallery` TEXT,
`tags` VARCHAR(255) DEFAULT '',
`sort_order` INT DEFAULT 0,
`status` TINYINT DEFAULT 1,
`created_at` VARCHAR(20) DEFAULT '',
`layout` TEXT,
`mode` VARCHAR(16) NOT NULL DEFAULT 'fixed',
KEY idx_products_category_id (category_id),
KEY idx_products_slug (slug),
KEY idx_products_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `news` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`title` VARCHAR(200) NOT NULL,
`slug` VARCHAR(200) DEFAULT '',
`cover` VARCHAR(255) DEFAULT '',
`summary` TEXT,
`content` TEXT,
`author` VARCHAR(60) DEFAULT '',
`published_at` VARCHAR(20) DEFAULT '',
`status` TINYINT DEFAULT 1,
`views` INT DEFAULT 0,
`layout` TEXT,
`mode` VARCHAR(16) NOT NULL DEFAULT 'fixed',
KEY idx_news_slug (slug),
KEY idx_news_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `cases` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`title` VARCHAR(200) NOT NULL,
`slug` VARCHAR(200) DEFAULT '',
`customer` VARCHAR(120) DEFAULT '',
`industry` VARCHAR(60) DEFAULT '',
`cover` VARCHAR(255) DEFAULT '',
`summary` TEXT,
`content` TEXT,
`published_at` VARCHAR(20) DEFAULT '',
`sort_order` INT DEFAULT 0,
`status` TINYINT DEFAULT 1,
`views` INT DEFAULT 0,
`layout` TEXT,
`mode` VARCHAR(16) NOT NULL DEFAULT 'fixed',
KEY idx_cases_slug (slug),
KEY idx_cases_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `pages` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`slug` VARCHAR(120) DEFAULT '',
`title` VARCHAR(200) DEFAULT '',
`content` TEXT,
`layout` TEXT,
`mode` VARCHAR(16) NOT NULL DEFAULT 'fixed',
`updated_at` VARCHAR(20) DEFAULT '',
KEY idx_pages_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `banners` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`title` VARCHAR(200) DEFAULT '',
`subtitle` VARCHAR(255) DEFAULT '',
`image` VARCHAR(255) DEFAULT '',
`link` VARCHAR(255) DEFAULT '',
`sort_order` INT DEFAULT 0,
`status` TINYINT DEFAULT 1,
KEY idx_banners_status (status),
KEY idx_banners_sort (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `admin_users` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`username` VARCHAR(60) NOT NULL,
`password` VARCHAR(255) NOT NULL,
`name` VARCHAR(60) DEFAULT '',
`role` VARCHAR(20) DEFAULT 'user',
`crm_role` VARCHAR(20) DEFAULT 'none',
`psi_role` VARCHAR(20) DEFAULT 'none',
`crm_perms` TEXT,
`psi_perms` TEXT,
`status` TINYINT DEFAULT 1,
`created_at` VARCHAR(20) DEFAULT '',
KEY idx_admin_users_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/* ===================== CRM 客户管理系统 ===================== */
CREATE TABLE IF NOT EXISTS `crm_customers` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(120) NOT NULL,
`company` VARCHAR(160) DEFAULT '',
`contact` VARCHAR(60) DEFAULT '',
`phone` VARCHAR(40) DEFAULT '',
`email` VARCHAR(120) DEFAULT '',
`country` VARCHAR(60) DEFAULT '',
`type` VARCHAR(20) DEFAULT 'brand',
`source` VARCHAR(40) DEFAULT '',
`level` VARCHAR(20) DEFAULT 'C',
`customer_no` VARCHAR(40) DEFAULT '',
`industry` VARCHAR(20) DEFAULT '',
`region` VARCHAR(40) DEFAULT '',
`credit_limit` DECIMAL(12,2) DEFAULT 0,
`status` VARCHAR(20) DEFAULT 'lead',
`remark` TEXT,
`owner` VARCHAR(60) DEFAULT '',
`created_at` VARCHAR(20) DEFAULT '',
KEY idx_customers_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `crm_contacts` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`customer_id` INT NOT NULL,
`name` VARCHAR(80) NOT NULL,
`title` VARCHAR(80) DEFAULT '',
`phone` VARCHAR(60) DEFAULT '',
`email` VARCHAR(120) DEFAULT '',
`wechat` VARCHAR(60) DEFAULT '',
`is_primary` TINYINT DEFAULT 0,
`remark` TEXT,
`created_at` VARCHAR(20) DEFAULT '',
KEY idx_contacts_customer_id (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `crm_leads` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`customer_id` INT DEFAULT 0,
`title` VARCHAR(200) NOT NULL,
`amount` DECIMAL(12,2) DEFAULT 0,
`stage` VARCHAR(20) DEFAULT 'new',
`expected_close` VARCHAR(20) DEFAULT '',
`source` VARCHAR(30) DEFAULT '',
`probability` TINYINT DEFAULT 0,
`owner` VARCHAR(60) DEFAULT '',
`remark` TEXT,
`created_at` VARCHAR(20) DEFAULT '',
KEY idx_leads_customer_id (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `crm_followups` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`customer_id` INT DEFAULT 0,
`lead_id` INT DEFAULT 0,
`content` TEXT,
`next_at` VARCHAR(20) DEFAULT '',
`way` VARCHAR(20) DEFAULT '',
`result` VARCHAR(60) DEFAULT '',
`owner` VARCHAR(60) DEFAULT '',
`created_at` VARCHAR(20) DEFAULT '',
KEY idx_followups_customer_id (customer_id),
KEY idx_followups_lead_id (lead_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/* ===================== 进销存 PSI 系统 ===================== */
CREATE TABLE IF NOT EXISTS `psi_materials` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`code` VARCHAR(40) DEFAULT '',
`name` VARCHAR(160) NOT NULL,
`spec` VARCHAR(120) DEFAULT '',
`unit` VARCHAR(10) DEFAULT '个',
`category` VARCHAR(40) DEFAULT '',
`composition` VARCHAR(60) DEFAULT '',
`weight_gsm` DECIMAL(8,2) DEFAULT 0,
`width_cm` DECIMAL(8,2) DEFAULT 0,
`color` VARCHAR(40) DEFAULT '',
`batch_no` VARCHAR(40) DEFAULT '',
`stock` DECIMAL(12,2) DEFAULT 0,
`price` DECIMAL(10,2) DEFAULT 0,
`supplier_id` INT DEFAULT 0,
`remark` TEXT,
`created_at` VARCHAR(20) DEFAULT '',
KEY idx_materials_supplier_id (supplier_id),
KEY idx_materials_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `psi_products` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`code` VARCHAR(40) DEFAULT '',
`name` VARCHAR(160) NOT NULL,
`spec` VARCHAR(120) DEFAULT '',
`unit` VARCHAR(10) DEFAULT '件',
`category` VARCHAR(40) DEFAULT '',
`style_no` VARCHAR(40) DEFAULT '',
`color` VARCHAR(40) DEFAULT '',
`size_run` VARCHAR(60) DEFAULT '',
`season` VARCHAR(20) DEFAULT '',
`year` VARCHAR(10) DEFAULT '',
`stock` DECIMAL(12,2) DEFAULT 0,
`cost` DECIMAL(10,2) DEFAULT 0,
`price` DECIMAL(10,2) DEFAULT 0,
`remark` TEXT,
`created_at` VARCHAR(20) DEFAULT '',
KEY idx_psi_products_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `psi_suppliers` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(160) NOT NULL,
`contact` VARCHAR(60) DEFAULT '',
`phone` VARCHAR(40) DEFAULT '',
`country` VARCHAR(60) DEFAULT '',
`type` VARCHAR(20) DEFAULT '',
`grade` VARCHAR(20) DEFAULT '',
`ontime_rate` DECIMAL(5,2) DEFAULT 0,
`qc_rate` DECIMAL(5,2) DEFAULT 0,
`remark` TEXT,
`created_at` VARCHAR(20) DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `psi_purchases` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`order_no` VARCHAR(40) NOT NULL,
`supplier_id` INT DEFAULT 0,
`item_type` VARCHAR(10) DEFAULT 'material',
`item_id` INT DEFAULT 0,
`qty` DECIMAL(12,2) DEFAULT 0,
`price` DECIMAL(10,2) DEFAULT 0,
`amount` DECIMAL(12,2) DEFAULT 0,
`status` VARCHAR(20) DEFAULT 'pending',
`batch_no` VARCHAR(40) DEFAULT '',
`expected_at` VARCHAR(20) DEFAULT '',
`remark` TEXT,
`created_at` VARCHAR(20) DEFAULT '',
KEY idx_purchases_supplier_id (supplier_id),
KEY idx_purchases_order_no (order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `psi_sales` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`order_no` VARCHAR(40) NOT NULL,
`customer` VARCHAR(160) DEFAULT '',
`channel` VARCHAR(20) DEFAULT 'domestic',
`item_id` INT DEFAULT 0,
`qty` DECIMAL(12,2) DEFAULT 0,
`price` DECIMAL(10,2) DEFAULT 0,
`amount` DECIMAL(12,2) DEFAULT 0,
`status` VARCHAR(20) DEFAULT 'pending',
`region` VARCHAR(40) DEFAULT '',
`batch_no` VARCHAR(40) DEFAULT '',
`remark` TEXT,
`created_at` VARCHAR(20) DEFAULT '',
KEY idx_sales_order_no (order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `psi_stock_moves` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`item_type` VARCHAR(10) DEFAULT 'product',
`item_id` INT DEFAULT 0,
`direction` VARCHAR(10) DEFAULT 'in',
`qty` DECIMAL(12,2) DEFAULT 0,
`ref_no` VARCHAR(40) DEFAULT '',
`batch_no` VARCHAR(40) DEFAULT '',
`remark` TEXT,
`created_at` VARCHAR(20) DEFAULT '',
KEY idx_stock_moves_item (item_type, item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `settings` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`skey` VARCHAR(120) NOT NULL,
`sval` TEXT,
`sgroup` VARCHAR(40) DEFAULT 'site',
KEY idx_settings_sgroup (sgroup)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `orders` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`order_no` VARCHAR(40) NOT NULL,
`product_id` INT DEFAULT 0,
`product_name` VARCHAR(200) DEFAULT '',
`customer_name` VARCHAR(120) DEFAULT '',
`phone` VARCHAR(40) DEFAULT '',
`email` VARCHAR(120) DEFAULT '',
`qty` INT DEFAULT 1,
`amount` DECIMAL(10,2) DEFAULT 0,
`channel` VARCHAR(20) DEFAULT '',
`status` VARCHAR(20) DEFAULT 'pending',
`gateway_trade_no` VARCHAR(120) DEFAULT '',
`paid_at` VARCHAR(20) DEFAULT '',
`created_at` VARCHAR(20) DEFAULT '',
KEY idx_orders_order_no (order_no),
KEY idx_orders_status (status),
KEY idx_orders_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `payments` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`order_id` INT DEFAULT 0,
`order_no` VARCHAR(40) NOT NULL,
`channel` VARCHAR(20) DEFAULT '',
`amount` DECIMAL(10,2) DEFAULT 0,
`trade_no` VARCHAR(120) DEFAULT '',
`status` VARCHAR(20) DEFAULT '',
`created_at` VARCHAR(20) DEFAULT '',
`paid_at` VARCHAR(20) DEFAULT '',
KEY idx_payments_order_id (order_id),
KEY idx_payments_order_no (order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `psi_events` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`sys` VARCHAR(20) NOT NULL DEFAULT 'psi',
`type` VARCHAR(40) NOT NULL DEFAULT '',
`level` VARCHAR(20) NOT NULL DEFAULT 'urgent',
`title` VARCHAR(255) NOT NULL DEFAULT '',
`body` TEXT,
`url` VARCHAR(255) NOT NULL DEFAULT '',
`ref_no` VARCHAR(64) NOT NULL DEFAULT '',
`recipients` TEXT,
`channels` VARCHAR(255) NOT NULL DEFAULT '["inapp"]',
`read_by` TEXT,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY idx_events_sys (sys),
KEY idx_events_level (level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Reference in New Issue View Git Blame Copy Permalink