mysql - 大写所有行的第一个字母 MySQL

Question

我需要运行一个脚本来更新“given_name”列并将所有首字母更改为大写..

我目前有这段代码..但没有任何反应..

DROP PROCEDURE IF EXISTS CAPITALIZE;
DELIMITER $$

CREATE PROCEDURE CAPITALIZE()
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 1;
SELECT COUNT(*) INTO n FROM users;
-- SET n = (SELECT COUNT(*) FROM users);
SET i=0;
WHILE (i<=n) DO 
  UPDATE users SET given_name = CONCAT( UPPER(SUBSTR(given_name,1,1)), SUBSTR(given_name,2)) WHERE user_id = n;
  UPDATE users SET last_name = CONCAT( UPPER(SUBSTR(last_name,1,1)), SUBSTR(last_name,2)) WHERE user_id = n;

  SET i = (i + 1);
END WHILE;
END $$
DELIMITER ;

Answer 1

正如@Drew 所提到的，您最好只对整个 users 表执行一揽子 UPDATE 语句:

UPDATE users
SET given_name = CONCAT( UPPER(SUBSTR(given_name,1,1)), LOWER(SUBSTR(given_name,2))),
    last_name = CONCAT( UPPER(SUBSTR(last_name,1,1)), LOWER(SUBSTR(last_name,2)))

要关闭阻止上述查询工作的安全更新模式，请键入:

SET SQL_SAFE_UPDATES = 0;

这是一个演示上述查询的工作 fiddle :

SQLFiddle