php CREATE TABLE IF NOT EXISTS 失败，表已经存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16554545/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CREATE TABLE IF NOT EXISTS fails with table already exists
提问by Samuel Stiles
I have the following code:
我有以下代码：
$db_host = 'localhost';
$db_port = '3306';
$db_username = 'root';
$db_password = 'root';
$db_primaryDatabase = 'dsl_ams';
// Connect to the database, using the predefined database variables in /assets/repository/mysql.php
$dbConnection = new mysqli($db_host, $db_username, $db_password, $db_primaryDatabase);
// If there are errors (if the no# of errors is > 1), print out the error and cancel loading the page via exit();
if (mysqli_connect_errno()) {
printf("Could not connect to MySQL databse: %s\n", mysqli_connect_error());
exit();
}
$queryCreateUsersTable = "CREATE TABLE IF NOT EXISTS `USERS` (
`ID` int(11) unsigned NOT NULL auto_increment,
`EMAIL` varchar(255) NOT NULL default '',
`PASSWORD` varchar(255) NOT NULL default '',
`PERMISSION_LEVEL` tinyint(1) unsigned NOT NULL default '1',
`APPLICATION_COMPLETED` boolean NOT NULL default '0',
`APPLICATION_IN_PROGRESS` boolean NOT NULL default '0',
PRIMARY KEY (`ID`)
)";
if(!$dbConnection->query($queryCreateUsersTable)){
echo "Table creation failed: (" . $dbConnection->errno . ") " . $dbConnection->error;
}
Which outputs...
哪个输出...
Table creation failed: (1050) Table 'dsl_ams.USERS' already exists
Table creation failed: (1050) Table 'dsl_ams.用户' already exists
What I don't understand is: isn't IF NOT EXISTSsupposed to cancel the execution of the SQL query if that table already exists? In other words, if the table exists, shouldn't it exit that if statement and not echo anything out at all, and not attempt to execute the query?
我不明白的是：IF NOT EXISTS如果该表已经存在，不应该取消 SQL 查询的执行吗？换句话说，如果该表存在，它是否应该退出该 if 语句并且根本不回显任何内容，并且不尝试执行查询？
Just trying to find the best way to "create a table if it doesn't exist" without outputting anything to the user.
只是试图找到“如果表不存在则创建表”的最佳方法，而不向用户输出任何内容。
回答by Josh Balcitis
Try this
尝试这个
$query = "SELECT ID FROM USERS";
$result = mysqli_query($dbConnection, $query);
if(empty($result)) {
$query = "CREATE TABLE USERS (
ID int(11) AUTO_INCREMENT,
EMAIL varchar(255) NOT NULL,
PASSWORD varchar(255) NOT NULL,
PERMISSION_LEVEL int,
APPLICATION_COMPLETED int,
APPLICATION_IN_PROGRESS int,
PRIMARY KEY (ID)
)";
$result = mysqli_query($dbConnection, $query);
}
This checks to see if anything is in the table and if it returns NULLyou don't have a table.
这将检查表中是否有任何内容，如果返回，则NULL您没有表。
Also there is no BOOLEANdatatype in mysql, you should INTand just set it to 1 or 0 when inserting into the table. You also don't need single quotes around everything, just when you are hardcoding data into the query.
BOOLEANmysql 中也没有数据类型，您应该INT在插入表时将其设置为 1 或 0。您也不需要在所有内容周围使用单引号，只是在您将数据硬编码到查询中时。
Like this...
像这样...
$query = "INSERT INTO USERS (EMAIL, PASSWORD, PERMISSION_LEVEL, APPLICATION_COMPLETED, APPLICATION_IN_PROGRESS) VALUES ('[email protected]', 'fjsdfbsjkbgs', 0, 0, 0)";
Hope this helps.
希望这可以帮助。
回答by Eduardo Vieira
To avoid outputting anything, test for the table in your php before trying to create the table. For example,
为避免输出任何内容，请在尝试创建表之前在 php 中测试该表。例如，
$querycheck='SELECT 1 FROM `USERS`';
$query_result=$dbConnection->query($querycheck);
if ($query_result !== FALSE)
{
// table exists
} else
{
// table does not exist, create here.
}
Best wishes,
最好的祝愿，
回答by Aminah Nuraini
How about you only show the error if the error number is not 1050?
如果错误号不是 1050，你只显示错误怎么样？
if(!$dbConnection->query($queryCreateUsersTable)){
if($dbConnection->errno != 1050){
echo "Table creation failed: (" . $dbConnection->errno . ") " . $dbConnection->error;
}
}
