MySQL – IF 함수에 대해서

MySQL IF 함수에 대해서 알아보도록 하겠습니다. 어떠한 조건에 일치하면 어떠한 값을 반환하고 아니면 또 다른 값을 반환할 수 있는 조건 함수입니다.


MySQL 함수를 소개하자면, MySQL IF 함수는 조건문으로 어떠한 값을 컨트롤 할 수 있는 유일한 함수 중 하나이다.IF함수는 때로는 IF ELSE 또는 IF THEN ELSE 함수가 따라다닌다.

IF 함수의 문법은 아래와 같다.

IF(expr,if_true_expr,if_false_expr)

만약 expr이 NULL이 아니고, expr*이 0이 아니면, **expr(표현)TRUE로 된다.그렇게 되면 if_true_expr이 실행되고 그렇지 안다면 if_false_expr이 실행된다. IF함수는 숫자 타입이나 문자열 타입으로 반환된다.

예로 아래와 같이 사용할 수 있다.

SELECT IF(1 = 2,'true','false'); -- false
SELECT IF(1 = 1,' true','false'); -- true

ref

MYSQL – 테이블 상태 보기

테이블에 대한 정보 보기

SHOW TABLE STATUS

테이블 디자인

DESCRIBE table

테이블 인코딩 타입 바꾸기

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

SQL – trigger, update timestamp when row is updated.

Ref

Sometime we need to update timestamp-column when row is changed. there are a lot of solution that you do. figure out about it…
Like this.
one way to just do updating sql or other way to use trigger.
I am going to tell you about trigger is good sometime is not.

I got following post from stackoverflow, some people uploaded
In Mysql, do like the following thing.

create table ab (
  id int, 
  changeTimestamp timestamp 
    NOT NULL 
    default CURRENT_TIMESTAMP 
    on update CURRENT_TIMESTAMP 
);

but In PostgreSQL is not worked. so there is other solution i got from stackoverflow. you can refer the following thing.
comment
Create a function that updates the changetimestamp column of a table like so:

CREATE OR REPLACE FUNCTION update_changetimestamp_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW.changetimestamp = now(); 
   RETURN NEW;
END;
$$ language 'plpgsql';

Create a trigger on the table that calls the update_changetimestamp_column() function whenever an update occurs like so:

CREATE TRIGGER update_ab_changetimestamp BEFORE UPDATE
ON ab FOR EACH ROW EXECUTE PROCEDURE 
update_changetimestamp_column();

That’s the solution i got. if you need more information, just refer the top of url. thanks for reading.

MySQL > How to change root password on Mevericks

I don’t know why the message that is the following thing is shown when i try to login on console.

Access denied for user ‘root’@’localhost’ (using password: NO)

So I’ve tried few ways.

These things didn’t work for me.

  1. mysqladmin -u root password ‘newpw’

  2. when i logged in just ‘mysql’ and then typed like those. 2.1 UPDATE mysql.user SET Password=PASSWORD(‘MyNewPass’) WHERE User=’root’; 2.2 FLUSH PRIVILEGES;

The following things work for me.

You’ve to use try /usr/local/mysql/bin/mysqld_safe

and then

  1. UPDATE mysql.user SET Password=PASSWORD(‘MyNewPass’) WHERE User=’root’;
  2. FLUSH PRIVILEGES;

You can see the successful message.

OS X – My SQL Socket Error.

I was getting some error while execute mysql.

It’s just socket error.

If you have /tmp/mysql.sock but no /var/mysql/mysql.sock then…

# cd /var 
# mkdir mysql
# cd mysql
# ln -s /tmp/mysql.sock mysql.sock

If you have /var/mysql/mysql.sock but no /tmp/mysql.sock then

# cd /tmp
# ln -s /var/mysql/mysql.sock mysql.sock

You will need permissions to create the directory and link, so just prefix the commands above with sudo if necessary.

you got this site :
http://stackoverflow.com/questions/4219970/warning-mysql-connect-2002-no-such-file-or-directory-trying-to-connect-vi

MySQL – Setting

php locate : /private/etc/php.ini

my.cnf : /etc/my.cnf
mysql socket : /tmp/mysql.sock
helper link
mysql helper link

mac lion webserver :

uninstall mysql on mac
[code]
This was a life saver!

sudo rm /usr/local/mysql
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
edit /etc/hostconfig and remove the line MYSQLCOM=-YES-
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm -rf /var/db/receipts/com.mysql.*
[/code]

setting root password
[code]
#mysql -uroot -p

mysql> use mysql
Database changed

mysql>update user set password=password(‘new_password’) where user=’root’;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql>flush privileges;
Query OK, 0 rows affected (0.00 sec)
[/code]