mysql 存储过程 用户_MySQL存储过程创建用户

I am trying to create a stored procedure to create user in mysql server 5.6.21 but it throws the following error:

Error Code: 1445. Not allowed to set autocommit from a stored function

or trigger.

the code below:

CREATE PROCEDURE `add_User`(IN p_Name VARCHAR(45), IN p_Passw VARCHAR(200))

BEGIN

DECLARE flag_exist TINYINT(1) DEFAULT 0;

SELECT COUNT(*)

INTO flag_exist

FROM

`mysql`.`USER`

WHERE

`mysql`.`USER`.`User` = p_Name;

IF flag_exist = 0 THEN

CREATE USER p_Name;

GRANT ALL PRIVILEGES ON *.* TO p_Name;

SET PASSWORD FOR p_Name = PASSWORD (p_Passw);

FLUSH PRIVILEGES;

/* NO FOUNT

INSERT INTO user VALUES

('localhost',p_Name,PASSWORD(p_Passw),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

*/

END IF;

END

解决方案

Maybe this will give you some ideas:

DELIMITER $$

DROP PROCEDURE IF EXISTS `add_User`$$

CREATE PROCEDURE `add_User`(IN `p_Name` VARCHAR(45), IN `p_Passw` VARCHAR(200))

BEGIN

DECLARE `_HOST` CHAR(14) DEFAULT '@\'localhost\'';

SET `p_Name` := CONCAT('\'', REPLACE(TRIM(`p_Name`), CHAR(39), CONCAT(CHAR(92), CHAR(39))), '\''),

`p_Passw` := CONCAT('\'', REPLACE(`p_Passw`, CHAR(39), CONCAT(CHAR(92), CHAR(39))), '\'');

SET @`sql` := CONCAT('CREATE USER ', `p_Name`, `_HOST`, ' IDENTIFIED BY ', `p_Passw`);

PREPARE `stmt` FROM @`sql`;

EXECUTE `stmt`;

SET @`sql` := CONCAT('GRANT ALL PRIVILEGES ON *.* TO ', `p_Name`, `_HOST`);

PREPARE `stmt` FROM @`sql`;

EXECUTE `stmt`;

DEALLOCATE PREPARE `stmt`;

FLUSH PRIVILEGES;

END$$

DELIMITER ;