mysql

数据类型

varchar(50)

5.0版本以上，varchar(50)，指的是50字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放50个

数值

约束

索引

联合索引

实例

插入100万条随机的百度地图坐标数据吗

-- 1. 创建数据库
CREATE DATABASE IF NOT EXISTS baidu_coords
  DEFAULT CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;
USE baidu_coords;

-- 2. 创建表
CREATE TABLE IF NOT EXISTS coords_bd09 (
  id      BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  bd_lng  DECIMAL(9,6) NOT NULL,  -- 经度
  bd_lat  DECIMAL(8,6) NOT NULL,  -- 纬度
  INDEX idx_bd_coords (bd_lng, bd_lat)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. 定义存储过程：总量 total，批大小 batch_size
DELIMITER $$
CREATE PROCEDURE fill_coords_bd09(IN total INT, IN batch_size INT)
BEGIN
  DECLARE inserted INT DEFAULT 0;
  WHILE inserted < total DO
    -- 利用 10^4 行的笛卡尔积临时表，一次生成 batch_size 条随机坐标
    INSERT INTO coords_bd09 (bd_lng, bd_lat)
    SELECT
      ROUND(113 + RAND() * 0.5, 6),  -- 假设经度在 [113,113.5) 范围
      ROUND(23  + RAND() * 0.5, 6)   -- 假设纬度在 [23,23.5) 范围
    FROM 
      (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
       UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) t1,
      (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
       UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) t2,
      (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
       UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) t3,
      (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
       UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) t4
    LIMIT batch_size;

    SET inserted = inserted + batch_size;
  END WHILE;
END$$
DELIMITER ;

-- 4. 调用存储过程，插入 100 万 条，分批 1 万 条
CALL fill_coords_bd09(1000000, 10000);

把原来的 lng/lat 直接替换为 GCJ-02 坐标

--（1）确保已经有下面两个函数，如果还没建，请先执行）
DELIMITER $$
CREATE FUNCTION bd09togcj02_lng(bd_lng DOUBLE, bd_lat DOUBLE) 
RETURNS DOUBLE DETERMINISTIC
BEGIN
  DECLARE x DOUBLE; DECLARE y DOUBLE;
  DECLARE z DOUBLE; DECLARE theta DOUBLE;
  SET x = bd_lng - 0.0065;
  SET y = bd_lat - 0.006;
  SET z     = SQRT(x*x + y*y) - 0.00002 * SIN(y * PI());
  SET theta = ATAN2(y, x) - 0.000003 * COS(x * PI());
  RETURN ROUND(z * COS(theta), 6);
END$$

CREATE FUNCTION bd09togcj02_lat(bd_lng DOUBLE, bd_lat DOUBLE) 
RETURNS DOUBLE DETERMINISTIC
BEGIN
  DECLARE x DOUBLE; DECLARE y DOUBLE;
  DECLARE z DOUBLE; DECLARE theta DOUBLE;
  SET x = bd_lng - 0.0065;
  SET y = bd_lat - 0.006;
  SET z     = SQRT(x*x + y*y) - 0.00002 * SIN(y * PI());
  SET theta = ATAN2(y, x) - 0.000003 * COS(x * PI());
  RETURN ROUND(z * SIN(theta), 6);
END$$
DELIMITER ;

--（2）直接用 UPDATE 覆盖原有 lng/lat
UPDATE coords_bd09
SET
  lng = bd09togcj02_lng(lng, lat),
  lat = bd09togcj02_lat(lng, lat);