MySQL broken Korean (UTF8)

MySQL 한글깨짐현상 제거 ( UTF8 )

한글이나 한자가 테이블상에서 깨져보임.
DB에 들어간 데이터를 확인하면 ??와 같이 깨져보임.

-> 이유 : 해당 table의 칼럼이 utf8로 지정되지 않아서 생기는 현상.

1. Mysql characterset을 utf8로 변경

/etc/my.cnf에 아래 내용 추가
————————————
[mysql]
default-character-set = utf8

[client]
default-character-set = utf8

[mysqld]
character-set-client-handshake=FALSE
init_connect=”SET collation_connection = utf8_general_ci”
init_connect=”SET NAMES utf8″
character-set-server = utf8
collation-server = utf8_general_ci

[mysqldump]
default-character-set = utf8
————————————

2. 테이블의 캐릭터셋을 변경하는 방법

ALTER TABLE table_name convert to charset utf8;

3. jdbc 접속정보에 utf8로 접속하도록 파라미터를 추가한다.

jdbcUrl=”jdbc:mysql://localhost/dbname?useUnicode=yes&characterEncoding=UTF-8&autoReconnect=true”

[출처] MySQL 한글깨짐현상 제거 ( UTF8 )|작성자 원두중독

asp 2 dimension array

Dim arr(3, 4)
‘4행 5열 배열
‘0부터 시작하므로 행은 0,1,2,3까지 총 4행
‘0부터 시작하므로 열은 0,1,2,3,4 까지 총 5열

numrows = UBound(arr, 1) ‘행
numcols = UBound(arr, 2) ‘열
cnt = 0

For i = 0 To numrows ‘행
For j = 0 To numcols ‘열
arr(i, j) = cnt
‘ response.write “{“& i &”,”&j&”}”
Response.Write(“{ ” & arr(i, j) & ” }”)
cntcnt = cnt + 1
Next
Response.Write(“<br /><br />”)
Next

javascript 2 dimesion array definition

var myArray = new Array( new Array(), new Array(),new Array(),new Array(),new Array(),new Array(),new Array(),new Array() );

myArray[0][0] = 1;
myArray[0][1] = 43;
myArray[0][2] = 14;
myArray[0][3] = 34;
myArray[0][4] = 9;
myArray[0][5] = 5;
myArray[0][6] = 26;
myArray[0][7] = 11;

myArray[1][0] = 10;
myArray[1][1] = 58;
myArray[1][2] = 38;
myArray[1][3] = 54;
myArray[1][4] = 61;
myArray[1][5] = 60;
myArray[1][6] = 41;
myArray[1][7] = 19;

myArray[2][0] = 13;
myArray[2][1] = 49;
myArray[2][2] = 30;
myArray[2][3] = 55;
myArray[2][4] = 37;
myArray[2][5] = 63;
myArray[2][6] = 22;
myArray[2][7] = 36;

myArray[3][0] = 25;
myArray[3][1] = 17;
myArray[3][2] = 21;
myArray[3][3] = 51;
myArray[3][4] = 42;
myArray[3][5] = 3;
myArray[3][6] = 27;
myArray[3][7] = 24;

myArray[4][0] = 44;
myArray[4][1] = 28;
myArray[4][2] = 50;
myArray[4][3] = 32;
myArray[4][4] = 57;
myArray[4][5] = 48;
myArray[4][6] = 18;
myArray[4][7] = 46;

myArray[5][0] = 6;
myArray[5][1] = 47;
myArray[5][2] = 64;
myArray[5][3] = 40;
myArray[5][4] = 59;
myArray[5][5] = 29;
myArray[5][6] = 4;
myArray[5][7] = 7;

myArray[6][0] = 33;
myArray[6][1] = 31;
myArray[6][2] = 56;
myArray[6][3] = 62;
myArray[6][4] = 53;
myArray[6][5] = 39;
myArray[6][6] = 52;
myArray[6][7] = 15;

myArray[7][0] = 12;
myArray[7][1] = 45;
myArray[7][2] = 35;
myArray[7][3] = 16;
myArray[7][4] = 20;
myArray[7][5] = 8;
myArray[7][6] = 23;
myArray[7][7] = 2;

for (i = 0; i < myArray.length ;i++ )
{
var s = 0;
var n = 0;
for (j = 0; j < myArray[i].length ;j++ )
{
document.write(myArray[i][j]) ;
s = s + myArray[i][j];
n = n + myArray[j][i];

}
document.write(” : ” + n );
document.write(” = ” + s +”<br>”);

}

bar of changes

천(天)

지(地)

수(水)

산(山)

풍(風)

화(火)

뢰(雷)

택(澤)

천(天)

1
중천건

11
지천태

5
수천수

26
산천대축

9
풍천소축

14
화천대유

34
뇌천대장

43
택천쾌

지(地)

12
천지비

2
중지곤

8
수지비

23
산지박

20
풍지관

35
화지진

16
뇌지예

45
택지췌

수(水)

6
천수송

7
지수사

29
중수감

4
산수몽

59
풍수환

64
화수미제

40
뇌수해

47
택수곤

산(山)

33
천산돈

15
지산겸

39
수산건

52
중산간

53
풍산점

56
화산려

62
뇌산 소과

31
택산함

풍(風)

44
천풍구

46
지풍승

48
수풍정

18
산풍고

57
중풍손

50
화풍정

32
뇌풍항

28
택풍대과

화(火)

13
천화동인

36
지화명이

63
수화기제

22
산화비

37
풍화가인

30
중화리

55
뇌화풍

49
택화혁

뢰(雷)

25
천뢰무망

24
지뢰복

3
수뢰둔

27
산뢰이

42
풍뢰익

21
화뢰서합

51
중뢰진

17
택뢰수

택(澤)

10
천택리

19
지택 임

60
수택절

41
산택손

61
풍택중부

38
화택규

54
뇌택귀매

58
중택태

mssql to mysql conversion

mssql to mysql

http://www.mysqltutorial.org/mysql-data-types.aspx

1.  isnull ==> ifnull — 쿼리에서만 변경해야.

  1. index.asp 224 chk_DAU_Count > “0”    데이타 타입 미스매치 –> csng
  2. getdate() ==> now()
  3. top 3 ==> limit 3 or limit 0, 3
  4. dbo.  ==> 공백
  5. datediff(hh,ubb_indt,getdate())  ==> TIMESTAMPDIFF(HOUR, ubb_indt, now())
  6. convert(varchar(10),regdate,120)  ==> date_format(regdate,’%Y-%m-%d’)
  7. convert(varchar(10),regdate,112)  ==> date_format(regdate,’%Y%m%d’)
  8. view에서 –> end if ;  세미콜론 주의
  9. db connection 다수 –> 모두 찾아 대체 utf 포함.
  10. with(nolock) ==> 공백 , (nolock) ==>공백
  11. nCount from db return == > 타입 캐스팅 csng
  12. 현재 부모경로 사용 상태임 — > 수정 변경 필요
  13. mysql ltrim, rtrim 지원됨
  14. exec sp_xx ==> call sp_xx  프로시져 호출
  15. mysql 뷰생성시 from 절 뒤 서브쿼리는 사용 불가능하다
  16. TIME_TO_SEC(TIMEDIFF(‘2007-01-09 10:24:46′,’2007-01-09 10:23:46’))
  17. not in(select * frm table   limt)  ==> This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’
  18. select charindex(‘st’,’myteststring’)   0   ==> SELECT INSTR(‘myteststring’,’st’);  1  인덱스 주의

mssql to mysql

  1. default ‘getdate()’ –> ‘000-000-000’  ==> 다시 어플에서 now()  추가
  2. Visual Studio 2010 Tools for Office Runtime — mssql -> 엑셀 -> mysql 로 convert
  3. mysql for excel

mssql to mysql  : exists

IF EXISTS (SELECT 1 FROM Table WHERE FieldValue=”)

BEGIN

SELECT TableID FROM Table WHERE FieldValue=”

END

ELSE

BEGIN

INSERT INTO TABLE(FieldValue) VALUES(”)

SELECT SCOPE_IDENTITY() AS TableID

END

— rewritten for MySQL

IF (SELECT 1 = 1 FROM Table WHERE FieldValue=”) THEN

BEGIN

SELECT TableID FROM Table WHERE FieldValue=”;

END;

ELSE

BEGIN

INSERT INTO Table (FieldValue) VALUES(”);

SELECT LAST_INSERT_ID() AS TableID;

END;

END IF;

myql procedure

http://code.tutsplus.com/articles/an-introduction-to-stored-procedures-in-mysql-5–net-17843

 

DELIMITER //

CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))

BEGIN

SELECT *

FROM offices

WHERE country = countryName;

END //

DELIMITER ;

CALL GetOfficeByCountry(‘USA’)

Microsoft SQL Server Type Mapping

10.5.4 Microsoft SQL Server Type Mapping

Table 10.2 Type mapping

Source Type MySQL Type Comment
INT INT
TINYINT TINYINT UNSIGNED flag set in MySQL
SMALLINT SMALLINT
BIGINT BIGINT
BIT TINYINT(1)
FLOAT FLOAT Precision value is used for storage size in both
REAL FLOAT
NUMERIC DECIMAL
DECIMAL DECIMAL
MONEY DECIMAL
SMALLMONEY DECIMAL
CHAR CHAR/LONGTEXT Depending on its length. MySQL Server 5.5 and above can have CHAR columns with a length up to 255 characters. Anything larger is migrated as LONGTEXT
NCHAR CHAR/LONGTEXT Depending on its length. MySQL Server 5.5 and above can have VARCHAR columns with a length up to 65535 characters. Anything larger is migrated to one of the TEXT blob types. In MySQL, character set of strings depend on the column character set instead of the datatype.
VARCHAR VARCHAR/MEDIUMTEXT/LONGTEXT Depending on its length. MySQL Server 5.5 and above can have VARCHAR columns with a length up to 65535 characters. Anything larger is migrated to one of the TEXT blob types.
NVARCHAR VARCHAR/MEDIUMTEXT/LONGTEXT Depending on its length. MySQL Server 5.5 and above can have VARCHAR columns with a length up to 65535 characters. Anything larger is migrated to one of the TEXT blob types. In MySQL, character set of strings depend on the column character set instead of the datatype.
DATE DATE
DATETIME DATETIME
DATETIME2 DATETIME Date range in MySQL is ‘1000-01-01 00:00:00.000000’ to ‘9999-12-31 23:59:59.999999’. Note: fractional second values are only stored as of MySQL Server 5.6.4
SMALLDATETIME DATETIME
DATETIMEOFFSET DATETIME
TIME TIME
TIMESTAMP TIMESTAMP
ROWVERSION TIMESTAMP
BINARY BINARY/MEDIUMBLOB/LONGBLOB Depending on its length
VARBINARY VARBINARY/MEDIUMBLOB/LONGBLOB Depending on its length
TEXT VARCHAR/MEDIUMTEXT/LONGTEXT Depending on its length
NTEXT VARCHAR/MEDIUMTEXT/LONGTEXT Depending on its length
IMAGE TINYBLOB/MEDIUMBLOB/LONGBLOB Depending on its length
SQL_VARIANT not migrated There is not specific support for this datatype.
TABLE not migrated There is not specific support for this datatype.
HIERARCHYID not migrated There is not specific support for this datatype.
UNIQUEIDENTIFIER VARCHAR(64) A unique flag set in MySQL. There is not specific support for inserting unique identifier values.
SYSNAME VARCHAR(160)
XML TEXT
Source Type MySQL Type Comment

mssql vs mysql data type mapping

@@ mssql vs mysql data type mapping

http://dev.mysql.com/doc/workbench/en/wb-migration-database-mssql-typemapping.html

http://www.sqlines.com/sql-server-to-mysql/functions/convert_string

mariadb, mysql feartures

  1. timestamp default는 테이블에 오직 하나만 가능.
  2. 테이블 upt-date 는  timestap –> datetime으로 변경 default 제거, null 허용해 해결
  3. timestamp는 default 가능한 2038년에 만료됨.