CODEWITHSUNDEEP
mariadbmysql-workbenchcollation
Coder88
Coder88

Reputation: 1055

How do I set CHARACTER SET and COLLATE SET on tables on Workbench ERP?

I'm using school server, so we can't add new databases, we only use existing one, but we can add tables on that.

I want to use Danish character set with collation Danish, so that when ordering names they retrieved based on Danish alphabet.

How can I do this in Workbench ERP?

About the database server I use: Server type: MariaDB Server version: 10.5.8-MariaDB - MariaDB Server

I did alter the table using this:

ALTER TABLE Users CONVERT TO CHARACTER SET utf8 COLLATE utf8_danish_ci;

This works.

But how can I set it on Workbench ERP so that I don't have to update all tables every time after doing forward engineering?

I try to change every table: enter image description here

But the collation returns back to utf8_bin even if I save.

And what's explained in this vide: https://www.youtube.com/watch?v=DflA8G5OCtQ&ab_channel=TechBrothersIT

There's no default_collation variable in my case.

I tried this on PHP too:

$this->dbh = new PDO("mysql:host=$this->host;dbname=$this->dbname", $this->username, $this->password, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8' SET COLLATION 'utf8_danish_ci'"));

But then I get the error:

SQLSTATE[42000] [1064] 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 'SET COLLATION 'utf8_danish_ci' at line 1

How can I set charset and collation on Workbench ERP or PDO so that I don't have to update all tables every time after doing forward engineering?

Upvotes: 1

Views: 2885

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562310

Refer to https://mariadb.com/kb/en/set-names/

The syntax should be:

SET NAMES 'utf8' COLLATE 'utf8_danish_ci'

Upvotes: 1

Related Questions