sql怎样用TRIM去除字符串字段的首尾空格 sql字符串处理的简单教程

TRIM

TRIM

-- 去除首尾空格
SELECT TRIM(column_name) FROM table_name;

-- 去除首部空格
SELECT LTRIM(column_name) FROM table_name;

-- 去除尾部空格
SELECT RTRIM(column_name) FROM table_name;

users

username

username

WHERE username = 'john'

TRIM

-- 插入数据前去除空格
INSERT INTO users (username) VALUES (TRIM('  john  '));

-- 更新数据时去除空格
UPDATE users SET username = TRIM(username) WHERE id = 1;

TRIM

UPPER

LOWER

SUBSTRING

REPLACE

LENGTH

UPPER

SELECT * FROM users WHERE UPPER(username) = 'JOHN';

SUBSTRING

SELECT SUBSTRING('Hello World', 1, 5); -- 返回 'Hello'

REPLACE

SELECT REPLACE('Hello World', 'World', 'SQL'); -- 返回 'Hello SQL'

UPDATE

DELIMITER //
CREATE PROCEDURE trim_all_string_fields()
BEGIN
    DECLARE table_name VARCHAR(255);
    DECLARE column_name VARCHAR(255);
    DECLARE done INT DEFAULT FALSE;

    -- 获取所有表的游标
    DECLARE table_cursor CURSOR FOR
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = DATABASE()
        AND table_type = 'BASE TABLE';

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN table_cursor;

    read_tables: LOOP
        FETCH table_cursor INTO table_name;
        IF done THEN
            LEAVE read_tables;
        END IF;

        -- 获取当前表的所有字符串类型字段的游标
        SET @sql = CONCAT('
            DECLARE column_cursor CURSOR FOR
                SELECT column_name
                FROM information_schema.columns
                WHERE table_schema = DATABASE()
                AND table_name = ''', table_name, '''
                AND data_type IN (''varchar'', ''char'', ''text'', ''mediumtext'', ''longtext'');
        ');

        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        SET @sql = CONCAT('
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET @done_columns = TRUE;
            SET @done_columns = FALSE;
            OPEN column_cursor;

            read_columns: LOOP
                FETCH column_cursor INTO column_name;
                IF @done_columns THEN
                    LEAVE read_columns;
                END IF;

                -- 更新当前字段，去除首尾空格
                SET @update_sql = CONCAT(''UPDATE `', table_name, '` SET `', column_name, '` = TRIM(`', column_name, '`);'');
                PREPARE update_stmt FROM @update_sql;
                EXECUTE update_stmt;
                DEALLOCATE PREPARE update_stmt;

            END LOOP read_columns;

            CLOSE column_cursor;
        ');

        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END LOOP read_tables;

    CLOSE table_cursor;
END//
DELIMITER ;

CALL trim_all_string_fields();
DROP PROCEDURE trim_all_string_fields;

trim_all_string_fields

UPDATE