Short script to create tabel in MariaDB

I'm having some problem creating a short sql script for MariaDB.

  1. Why are both messages printed out although they are separated by a ELSE?
  2. 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.

%d bloggers like this: