I'm having some problem creating a short sql script for MariaDB.
- Why are both messages printed out although they are separated by a ELSE?
- Is the DELIMITER realy needed? I've found mixed opinions
Here the script:
CREATE DATABASE IF NOT EXISTS mydb;
USE mydb;
DELIMITER ;;
IF EXISTS ( SELECT 1
FROM information_schema.Tables
WHERE table_schema = 'mydb'
AND TABLE_NAME = 'users' ) THEN
\! echo "The table users already exists";
ELSE
CREATE TABLE users(
id VARCHAR(255) NOT NULL,
username VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_login TIMESTAMP
) ENGINE=INNODB;
\! echo "The table users has been created";
END IF;
;; DELIMITER ;
And here the error:
MariaDB [mydb]> source create-1.sql
Query OK, 0 rows affected, 1 warning (0.000 sec)
Database changed
The table users already exists
The table users has been created
ERROR 1064 (42000) at line 7 in file: 'create-1.sql': 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 'ELSE
CREATE TABLE users(
id VARCHAR(255) NOT NULL,
...' at line 6
ERROR 1064 (42000) at line 7 in file: 'create-1.sql': 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 'DELIMITER' at line 1
MariaDB [mydb]>
Read more here: https://stackoverflow.com/questions/66280537/short-script-to-create-tabel-in-mariadb
Content Attribution
This content was originally published by Agyla at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.