If you get a tattoo by the traditional hand-method used in Thailand, I can't tell you what that's like because I've never experienced it.
As far as Western tattoo methods, having a motorized needle repeatedly puncture the skin does hurt. Given how many people have tattoos, it's safe to say most people tolerate the pain fairly well.