Ændre datatype på eksisterende felt

ALTER TABLE tabelnavn MODIFY feltnavn ...;

Hvis du har adgang til en GUI (MySQL CC, PHPMyAdmin etc.) er det meget nemmere.

CHAR(255) ?

Er du sikker på at du ikke vil have VARCHAR(255) ?

ALTER TABEL myTabel MODIFY(titel CHAR(255));

hvad er forskellen på VARCHAR(255) og Char(255) ?

CHAR(255) = altid 255 tegn

VARCHAR(255) = 1 byte med længde + 0-255 tegn

VARCHAR både sparer plads hvis ikke alle værdier faktisk bruger 255 tegn og kan
undgå nogle trailing spaces forvirrer output i nogen sammenhænge

Hvis man har brugt fast størrelse typer i alle kolonner, giver det bedre performance og mere sikkerhed.

http://mysqld.active-venture.com/Static_format.html

tatic (Fixed-length) Table Characteristics

This is the default format. It's used when the table contains no VARCHAR, BLOB, or TEXT columns.

This format is the simplest and most secure format. It is also the fastest of the on-disk formats. The speed comes from the easy way data can be found on disk. When looking up something with an index and static format it is very simple. Just multiply the row number by the row length.

Also, when scanning a table it is very easy to read a constant number of records with each disk read.

The security is evidenced if your computer crashes when writing to a fixed-size MyISAM file, in which case myisamchk can easily figure out where each row starts and ends. So it can usually reclaim all records except the partially written one. Note that in MySQL all indexes can always be reconstructed:

    * All CHAR, NUMERIC, and DECIMAL columns are space-padded to the column width.
    * Very quick.
    * Easy to cache.
    * Easy to reconstruct after a crash, because records are located in fixed positions.
    * Doesn't have to be reorganized (with myisamchk) unless a huge number of records are deleted and you want to return free disk space to the operating system.
    * Usually requires more disk space than dynamic tables.

Se det lød jo umiddelbart meget overbevisende.

Men så begynder man at undre sig lidt. Sådan ville man da ikke selv programmere det.

Og så tester man.

Og testen viser:
  - ved max længde er CHAR og VARCHAR ca. lige hurtige
  - ved længder langt under max længde er VARCHAR hurtigere end CHAR

Så den artikel er noget sludder.

[og Chries det er ikke kritik af dig - det er ikke dig som har skrevet den artikel]

Field length = 25
CHAR INSERT : 12078
VARCHAR INSERT : 9656
CHAR SELECT : 6203
VARCHAR SELECT : 4891
CHAR INSERT : 9812
VARCHAR INSERT : 9547
CHAR SELECT : 5875
VARCHAR SELECT : 4859
CHAR INSERT : 9984
VARCHAR INSERT : 9594
CHAR SELECT : 5906
VARCHAR SELECT : 4844
Field length = 50
CHAR INSERT : 10141
VARCHAR INSERT : 9953
CHAR SELECT : 7687
VARCHAR SELECT : 7110
CHAR INSERT : 10203
VARCHAR INSERT : 9969
CHAR SELECT : 7937
VARCHAR SELECT : 6750
CHAR INSERT : 10141
VARCHAR INSERT : 9906
CHAR SELECT : 7953
VARCHAR SELECT : 6781
Field length = 100
CHAR INSERT : 10766
VARCHAR INSERT : 10844
CHAR SELECT : 11109
VARCHAR SELECT : 10141
CHAR INSERT : 10922
VARCHAR INSERT : 10718
CHAR SELECT : 11047
VARCHAR SELECT : 10141
CHAR INSERT : 10812
VARCHAR INSERT : 10781
CHAR SELECT : 11110
VARCHAR SELECT : 10140
Field length = 200
CHAR INSERT : 12343
VARCHAR INSERT : 12406
CHAR SELECT : 15938
VARCHAR SELECT : 15641
CHAR INSERT : 12406
VARCHAR INSERT : 12484
CHAR SELECT : 16016
VARCHAR SELECT : 15750
CHAR INSERT : 12391
VARCHAR INSERT : 12343
CHAR SELECT : 15579
VARCHAR SELECT : 15828
Field length = 255
CHAR INSERT : 13141
VARCHAR INSERT : 13297
CHAR SELECT : 22312
VARCHAR SELECT : 22125
CHAR INSERT : 13078
VARCHAR INSERT : 13172
CHAR SELECT : 21906
VARCHAR SELECT : 22078
CHAR INSERT : 13187
VARCHAR INSERT : 13328
CHAR SELECT : 21875
VARCHAR SELECT : 22141

import java.sql.*;

public class CharVersusVarchar {
    private final static int NREC = 100000;
    private final static int SELREP = 5;
    private final static String F1VAL = "01234567890123456789012345678901234567890123456789" +
                                        "01234567890123456789012345678901234567890123456789" +
                                        "01234567890123456789012345678901234567890123456789" +
                                        "01234567890123456789012345678901234567890123456789" +
                                        "01234567890123456789012345678901234567890123456789" +
                                        "01234567890123456789012345678901234567890123456789";
    private final static String F2VAL = "ABCDEFGHIJKLMNOPQRSTUVWXYABCDEFGHIJKLMNOPQRSTUVWXY" +
                                        "ABCDEFGHIJKLMNOPQRSTUVWXYABCDEFGHIJKLMNOPQRSTUVWXY" +
                                        "ABCDEFGHIJKLMNOPQRSTUVWXYABCDEFGHIJKLMNOPQRSTUVWXY" +
                                        "ABCDEFGHIJKLMNOPQRSTUVWXYABCDEFGHIJKLMNOPQRSTUVWXY" +
                                        "ABCDEFGHIJKLMNOPQRSTUVWXYABCDEFGHIJKLMNOPQRSTUVWXY" +
                                        "ABCDEFGHIJKLMNOPQRSTUVWXYABCDEFGHIJKLMNOPQRSTUVWXY";
    private static Connection con;
    private static int testCharInsert(int flen) throws SQLException {
        PreparedStatement pstmt = con.prepareStatement("INSERT INTO c VALUES(?,?)");
        long t1 = System.currentTimeMillis();
        for(int i = 0; i < NREC; i++) {
            pstmt.setString(1, F1VAL.substring(0, flen));
            pstmt.setString(2, F2VAL.substring(0, flen));
            pstmt.executeUpdate();
        }
        long t2 = System.currentTimeMillis();
        pstmt.close();
        return (int)(t2-t1);
    }
    private static int testVarcharInsert(int flen) throws SQLException  {
        PreparedStatement pstmt = con.prepareStatement("INSERT INTO vc VALUES(?,?)");
        long t1 = System.currentTimeMillis();
        for(int i = 0; i < NREC; i++) {
            pstmt.setString(1, F1VAL.substring(0, flen));
            pstmt.setString(2, F2VAL.substring(0, flen));
            pstmt.executeUpdate();
        }
        long t2 = System.currentTimeMillis();
        pstmt.close();
        return (int)(t2-t1);
    }
    private static int testCharSelect(int flen) throws SQLException  {
        Statement stmt = con.createStatement();
        long t1 = System.currentTimeMillis();
        for(int j = 0; j < SELREP; j++) {
            ResultSet rs = stmt.executeQuery("SELECT * FROM c");
            int n = 0;
            String f1 = "";
            String f2 = "";
            while(rs.next()) {
                f1 = rs.getString(1);
                f2 = rs.getString(2);
                n++;
            }
            if((n != NREC) ||
              !f1.equals(F1VAL.substring(0, flen)) ||
              !f2.equals(F2VAL.substring(0, flen))) {
                throw new RuntimeException("Ooops");
            }
        }
        long t2 = System.currentTimeMillis();
        stmt.close();
        return (int)(t2-t1);
    }
    private static int testVarcharSelect(int flen) throws SQLException  {
        Statement stmt = con.createStatement();
        long t1 = System.currentTimeMillis();
        for(int j = 0; j < SELREP; j++) {
            ResultSet rs = stmt.executeQuery("SELECT * FROM vc");
            int n = 0;
            String f1 = "";
            String f2 = "";
            while(rs.next()) {
                f1 = rs.getString(1);
                f2 = rs.getString(2);
                n++;
            }
            if((n != NREC) ||
              !f1.equals(F1VAL.substring(0, flen)) ||
              !f2.equals(F2VAL.substring(0, flen))) {
                throw new RuntimeException("Ooops");
            }
        }
        long t2 = System.currentTimeMillis();
        stmt.close();
        return (int)(t2-t1);
    }
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection("jdbc:mysql://localhost/Test", "", "");
        Statement stmt = con.createStatement();
        int[] flen = { 25, 50, 100, 200, 255 };
        for(int k = 0; k < flen.length; k++) {
            System.out.println("Field length = " + flen[k]);
            for(int i = 0; i < 3; i++) {
                stmt.executeUpdate("CREATE TABLE c (f1 CHAR(255), f2 CHAR(255))");
                stmt.executeUpdate("CREATE TABLE vc (f1 VARCHAR(255), f2 VARCHAR(255))");
                System.out.println("CHAR INSERT : " + testCharInsert(flen[k]));
                System.out.println("VARCHAR INSERT : " + testVarcharInsert(flen[k]));
                System.out.println("CHAR SELECT : " + testCharSelect(flen[k]));
                System.out.println("VARCHAR SELECT : " + testVarcharSelect(flen[k]));
                stmt.executeUpdate("DROP TABLE c");
                stmt.executeUpdate("DROP TABLE vc");
            }
        }
        stmt.close();
        con.close();
    }
}

Så kan folk selv teste.

indsætte et par rækker er næppe en test :) der skal tilfældige indsæt/slet i en andet størrelse orden til =) en linket liste er også hurtigere at søge i en end hash tabel ved få elementer.

Fra mysql.com selv:

http://dev.mysql.com/doc/mysql/en/Static_format.html

Static format is the default for MyISAM tables. It is used when the table contains no variable-length columns (VARCHAR, BLOB, or TEXT). Each row is stored using a fixed number of bytes.

Of the three MyISAM storage formats, static format is the simplest and most secure (least subject to corruption). It is also the fastest of the on-disk formats. The speed comes from the easy way that rows in the data file can be found on disk: When looking up a row based on a row number in the index, multiply the row number by the row length. Also, when scanning a table, it is very easy to read a constant number of records with each disk read operation.

The security is evidenced if your computer crashes while the MySQL server is writing to a fixed-format MyISAM file. In this case, myisamchk can easily determine where each row starts and ends, so it can usually reclaim all records except the partially written one. Note that MyISAM table indexes can always be reconstructed based on the data rows.

General characteristics of static format tables:

    * All CHAR, NUMERIC, and DECIMAL columns are space-padded to the column width.
    * Very quick.
    * Easy to cache.
    * Easy to reconstruct after a crash, because records are located in fixed positions.
    * Reorganization is unnecessary unless you delete a huge number of records and want to return free disk space to the operating system. To do this, use OPTIMIZE TABLE or myisamchk -r.
    * Usually require more disk space than for dynamic-format tables.

De 100000 var nok til at data røg på disk, så der er ingen grund til at tro at
det vil ændre sig med større data mængder.

Mener du helt seriøst at det betyder noget om der er slettet records ?

Og jeg har meget svært ved at se hvordan man kan kode et lookup af enkelt record
via index på en måde således at fast/variabel record længde skulle betyde noget.

Det er da interessant at teksten kommer fra MySQL docs.

Men jeg tror stadig at den er forkert.

dark>

Fik du ændret din tabel ?

Og et svar fra mig

dark ?

sorry