mysql 百分数转换小数

-- 假设有一个表 `data`，其中有一个百分数字段 `percentage`
CREATE TABLE data (
    id INT PRIMARY KEY,
    percentage VARCHAR(10)
);

-- 插入一些示例数据
INSERT INTO data (id, percentage) VALUES (1, '75%'), (2, '50%'), (3, '90%');

-- 将百分数转换为小数
SELECT id, CAST(REPLACE(percentage, '%', '') AS DECIMAL(5, 2)) / 100 AS decimal_percentage
FROM data;

SELECT id, CAST(REPLACE(percentage, '%', '') AS DECIMAL(5, 2)) / 100 AS decimal_percentage
FROM data
WHERE percentage REGEXP '^[0-9]+%$';

SELECT id, CAST(REPLACE(percentage, '%', '') AS DECIMAL(10, 4)) / 100 AS decimal_percentage
FROM data;