如何使用过程更改列的默认值 [英] How to alter the default value of a column using a procedure

问题描述

这是我当前的SQL的样子:

Here is what my current SQL looks like:

DELIMITER $$
CREATE PROCEDURE updateDefaultUserRole(
    IN rid_in INT
) BEGIN
    ALTER TABLE _users
    MODIFY rid INT(255) NOT NULL DEFAULT rid_in; -- Modify the columns default value
    UPDATE _users SET rid = rid_in WHERE rid < rid_in; -- Update all entries lower than the role ID.
END $$
DELIMITER ;

这是我的数据库_users表的样子:

Here is what my database _users table looks like:

CREATE TABLE `_users` (
  `uid` int(255) NOT NULL,
  `forname` varchar(40) NOT NULL,
  `surname` varchar(40) NOT NULL,
  `email` varchar(120) NOT NULL,
  `hash` varchar(60) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `rid` int(255) NOT NULL DEFAULT '2'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `_users`
  ADD PRIMARY KEY (`uid`),
  ADD KEY `rid` (`rid`);
  MODIFY `uid` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

我收到此错误:

您的SQL语法有错误；检查与您的MariaDB服务器版本相对应的手册，以在'rid_in附近使用正确的语法； -修改列的默认值

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'rid_in; -- Modify the columns default value

该行的链接:

ALTER TABLE _users
MODIFY rid INT(255) NOT NULL DEFAULT rid_in;

如果我更改此设置，请忽略rid_in值:

If I change this, ignoring the rid_in value:

ALTER TABLE _users
MODIFY rid INT(255) NOT NULL DEFAULT '1';

它工作正常，如何将rid_in输入绑定到查询?

It works fine, how can I bind the rid_in input to the query?

我的预期输出是能够用来更改默认用户角色值并将具有旧用户值的所有行更新为新行:

My expected output is to be able to use to change the default user role value and update all the rows with the old user value to the new one:

CALL updateDefaultUserRole(@someInt)

能够为每个用户更新默认值.

To be able to update the default value for each user.

推荐答案

您需要使用

You need to use Dynamic SQL here, as Default clause in the Alter Table will not be able to resolve the variable value:

DELIMITER $$
CREATE PROCEDURE updateDefaultUserRole(
    IN rid_in INT
) BEGIN

    -- generate the query string for Alter Table
    SET @alter_query_str = CONCAT('ALTER TABLE _users
                                   MODIFY rid INT(255) NOT NULL 
                                   DEFAULT ', 
                                  rid_in); -- Modify the columns default value
    -- prepare the query
    PREPARE stmt FROM @alter_query_str;
    -- execute the query
    EXECUTE stmt;
    -- deallocate the query
    DEALLOCATE PREPARE stmt;

    UPDATE _users SET rid = rid_in 
    WHERE rid < rid_in; -- Update all entries lower than the role ID.

END $$
DELIMITER ;

这篇关于如何使用过程更改列的默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！