如何使用 phpmyadmin 编写存储过程以及如何通过 php 使用它？

How to write a stored procedure using phpmyadmin and how to use it through php?

提问by Starx

I want to be able create stored procedures using phpMyAdminand later on use it through php.

我希望能够创建存储过程phpMyAdmin，然后通过 php 使用它。

But I dont know how to?

但我不知道怎么做？

From what I know, I found out that we cannot manage stored procedures through phpMyAdmin.

据我所知，我发现我们无法通过phpMyAdmin.

What other tool can manage stored procedure?

还有什么工具可以管理存储过程？

I am not even sure if it is better option to use stored procedure through PHP. Any suggestion?

我什至不确定通过 PHP 使用存储过程是否是更好的选择。有什么建议吗？

回答by wimvds

Since a stored procedure is created, altered and dropped using queries you actually CAN manage them using phpMyAdmin.

由于使用查询创建、更改和删除存储过程，您实际上可以使用 phpMyAdmin 管理它们。

To create a stored procedure, you can use the following (change as necessary) :

要创建存储过程，您可以使用以下内容（根据需要进行更改）：

CREATE PROCEDURE sp_test()
BEGIN
  SELECT 'Number of records: ', count(*) from test;
END//

And make sure you set the "Delimiter" field on the SQL tab to //.

并确保将 SQL 选项卡上的“分隔符”字段设置为 //。

Once you created the stored procedure it will appear in the Routines fieldset below your tables (in the Structure tab), and you can easily change/drop it.

创建存储过程后，它将出现在表下方的 Routines 字段集中（在 Structure 选项卡中），您可以轻松更改/删除它。

To use the stored procedure from PHP you have to execute a CALL query, just like you would do in plain SQL.

要使用 PHP 中的存储过程，您必须执行 CALL 查询，就像在普通 SQL 中所做的一样。

回答by Hammad Khan

I guess no one mentioned this so I will write it here. In phpMyAdmin 4.x, there is "Add Routine" link under "Routines" tab at the top row. This link opens a popup dialog where you can write your Stored procedure without worrying about delimiter or template.

我想没有人提到这一点，所以我会写在这里。在 phpMyAdmin 4.x 中，顶行的“例程”选项卡下有“添加例程”链接。此链接将打开一个弹出对话框，您可以在其中编写存储过程，而无需担心分隔符或模板。

Add Routine

添加例程

Note that for simple test stored procedure, you may want to drop the default parameter which is already given or you can simply set it with a value.

请注意，对于简单的测试存储过程，您可能希望删除已经给出的默认参数，或者您可以简单地设置一个值。

回答by Sourav

try this

尝试这个

delimiter ;;

drop procedure if exists test2;;

create procedure test2()

begin

select ‘Hello World';

end

;;

回答by Reto

I got it to work in phpAdmin , but only when I removed the "Number of records " phrase.

我让它在 phpAdmin 中工作，但只有当我删除“记录数”短语时。

In my version of phpAdmin I could see the box for changing the delimiters.

在我的 phpAdmin 版本中，我可以看到更改分隔符的框。

Also to see the procedure in the database i went to the phpAdmin home, then information_schema database and then the routines table.

还要查看数据库中的过程，我去了 phpAdmin 主页，然后是 information_schema 数据库，然后是例程表。

回答by Paul J

The new version of phpMyAdmin (3.5.1) has much better support for stored procedures; including: editing, execution, exporting, PHP code creation, and some debugging.

新版phpMyAdmin (3.5.1) 对存储过程的支持更好；包括：编辑、执行、导出、PHP 代码创建和一些调试。

Make sure you are using the mysqli extension in config.inc.php

确保你在 config.inc.php 中使用 mysqli 扩展

$cfg['Servers'][$i]['extension'] = 'mysqli';

Open any database, you'll see a new tab at the top called Routines, then select Add Routine.

打开任何数据库，您将在顶部看到一个名为Routines的新选项卡，然后选择Add Routine。

The first test I did produced the following error message:

我做的第一个测试产生了以下错误消息：

MySQL said: #1558 - Column count of mysql.proc is wrong. Expected 20, found 16. Created with MySQL 50141, now running 50163. Please use mysql_upgrade to fix this error.

MySQL 说：#1558 - mysql.proc 的列数错误。预计20，发现16。用MySQL 50141创建，现在运行50163。请使用mysql_upgrade修复此错误。

Ciuly's Blogprovides a good solution, assuming you have command line access. Not sure how you would fix it if you don't.

Ciuly 的博客提供了一个很好的解决方案，假设您具有命令行访问权限。不知道你会如何解决它，如果你不这样做。

回答by Jay Thakkar

1. delimiter ;;
2. CREATE PROCEDURE sp_number_example_records()
3. BEGIN
   SELECT count(id) from customer; END
4. ;;

1. 分隔符;;
2. 创建过程 sp_number_example_records()
3. BEGIN
   SELECT count(id) from customer; 结尾
4. ;;

回答by ronaldosantana

Try Toad for MySQL - its free and its great.

试用 Toad for MySQL - 它免费且功能强大。

回答by Delickate

On local server your following query will work

在本地服务器上，您的以下查询将起作用

DELIMITER |

CREATE PROCEDURE sample_sp_with_params (IN empId INT UNSIGNED, OUT oldName VARCHAR(20), INOUT newName VARCHAR(20))

BEGIN

SELECT `first name` into oldName FROM emp where id = empId;

UPDATE emp SET `first name`= newName where id = empId;

END

|

DELIMITER ;

but on production server it might not work. depend on mysql version you are using. I had a same problem on powweb server, i removed delimiter and begin keywords, it works fine. have a look at following query

但在生产服务器上它可能不起作用。取决于您使用的 mysql 版本。我在 powweb 服务器上遇到了同样的问题，我删除了分隔符并开始关键字，它工作正常。看看下面的查询

CREATE PROCEDURE adminsections( IN adminId INT UNSIGNED ) SELECT tbl_adminusersection.ads_name, tbl_adminusersection.ads_controller FROM tbl_adminusersectionright LEFT JOIN tbl_adminusersection ON ( tbl_adminusersectionright.adsr_ads_id = tbl_adminusersection.ads_id ) LEFT JOIN tbl_adminusers ON ( tbl_adminusersectionright.adsr_adusr_id = tbl_adminusers.admusr_id ) WHERE tbl_adminusers.admusr_id = adminId;

回答by Ajnabi

/*what is wrong with the following?*/
DELIMITER $$

CREATE PROCEDURE GetStatusDescr(
    in  pStatus char(1), 
    out pStatusDescr  char(10))
BEGIN 
    IF (pStatus == 'Y THEN
 SET pStatusDescr = 'Active';
    ELSEIF (pStatus == 'N') THEN
        SET pStatusDescr = 'In-Active';
    ELSE
        SET pStatusDescr = 'Unknown';
    END IF;

END$$

回答by Sandeep Taneja

All the answers above are making certain assumptions. There are basic problems in 1and1 and certain other hosting providers are using phpMyAdmin versions which are very old and have an issue that defined delimiter is ; and there is no way to change it from phpMyAdmin. Following are the ways

以上所有答案都是在做出某些假设。1and1 存在基本问题，某些其他托管服务提供商正在使用非常旧的 phpMyAdmin 版本，并且存在定义分隔符的问题；并且无法从 phpMyAdmin 更改它。以下是方法

1. Create a new PHPMyAdmin on the hosting provider. Attached link gives you idea http://internetbandaid.com/2009/04/05/install-phpmyadmin-on-1and1/
2. Go thru the complicated route of be able to access the your hosting providers mySQL from your dekstop. It is complicated but the best if you are a serious developer. here is a link to do it http://franklinstrube.com/blog/remote-mysql-administration-for-1and1/

1. 在托管服务提供商上创建一个新的 PHPMyAdmin。附加链接给你想法http://internetbandaid.com/2009/04/05/install-phpmyadmin-on-1and1/
2. 通过能够从您的桌面访问您的托管服务提供商 mySQL 的复杂路线。这很复杂，但如果您是一名认真的开发人员，那就最好了。这是一个链接http://franklinstrube.com/blog/remote-mysql-administration-for-1and1/

Hope this helps

希望这可以帮助