15seconds

Blog

  • 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 )|작성자 원두중독

  • mssql single user mode to multiuser

    use master
    GO

    select
    d.name,
    d.dbid,
    spid,
    login_time,
    nt_domain,
    nt_username,
    loginame
    from sysprocesses p
    inner join sysdatabases d
    on p.dbid = d.dbid
    where d.name = ‘database_name’
    GO

    kill 56 => kill the number in spid field
    GO

    exec sp_dboption ‘database_name’, ‘single user’, ‘FALSE’
    GO

  • 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
    중택태

  • HD sql

    고급 sql @@

    http://wiki.gurubee.net/pages/viewpage.action?pageId=26740935

    오라클 맥에 설치하기 리눅스 가상머신 이용
    https://dimitrisli.wordpress.com/2012/08/08/how-to-install-oracle-database-on-mac-os-any-version/

  • 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년에 만료됨.

  • mssql server 2005 paging

    mssql server 2005 paging

    SET @RowStart = @PageSize * (@Page-1) + 1;
    SET @RowEnd = @RowStart + @PageSize – 1 ;

    SELECT * FROM (

    SELECT RowNumber() OVER(order by USER_ID) As RowID,

    COUNT (USER_ID) OVER (PARTITION BY null) AS TOTAL_ROWS,

    name

    from usertbl

    ) As RowResults

    WHERE RowID Between @RowStart AND @RowEnd

    Order by RowID asc